Enter 2001 April/EnterCD4.iso/Update/SQL Server SP3/sql70sp3i.exe/INSTALL/sp3_tools.sql

Text (UTF-16) | 2000-10-18 | 223.7 KB | 2,480 lines

/*------------------------------------------------------------------------------

SP3_TOOLS.SQL

THIS SCRIPT TAKES THE TOOLS SYSTEM STORED PROCEDURES FROM 7.0, SP1, SP2 to SP3.

Changes in this file are organized as follows (please maintain):
    SYSTEM OBJECTS (INSTMSDB.SQL)

Notes:
+ Catalog-updates and sp_MS_upd_sysobj_category are enabled for the entire
    file.  Do not disable or re-enable them.  Please do not change set options.

------------------------------------------------------------------------------*/


--------------------------------------------------------------------------------
-- VERIFY Server is started in single-user-mode (catalog-updates enabled), and
--    start marking of system-objects.
--------------------------------------------------------------------------------
use master
go

exec dbo.sp_configure 'allow updates',1
go

reconfigure with override
go

exec sp_MS_upd_sysobj_category 1
go

--------------------------------------------------------------------------------
--.    Common system objects (instmsdb.sql)
--------------------------------------------------------------------------------
use msdb
go

/**************************************************************/
/* SYSCACHEDCREDENTIALS                                       */
/*                                                            */
/**************************************************************/

IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysobjects
                WHERE (name = N'syscachedcredentials')
                  AND (type = 'U')))
BEGIN
  CREATE TABLE syscachedcredentials
  (
  login_name          sysname      NOT NULL PRIMARY KEY,
  has_server_access   BIT          NOT NULL DEFAULT 0,
  is_sysadmin_member  BIT          NOT NULL DEFAULT 0,
  cachedate           DATETIME     NOT NULL DEFAULT getdate()
  )
END
go

/**************************************************************/
/* SP_SQLAGENT_HAS_SERVER_ACCESS                              */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_sqlagent_has_server_access...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = 'sp_sqlagent_has_server_access')
              AND (type = 'P')))
  DROP PROCEDURE sp_sqlagent_has_server_access
go
CREATE PROCEDURE sp_sqlagent_has_server_access
  @login_name         sysname = NULL,
  @is_sysadmin_member INT     = NULL OUTPUT
AS
BEGIN
  DECLARE @has_server_access BIT
  DECLARE @is_sysadmin       BIT
  DECLARE @actual_login_name sysname
  DECLARE @cachedate         DATETIME

  SET NOCOUNT ON

  SELECT @cachedate = NULL

  -- remove expired entries from the cache
  DELETE msdb.dbo.syscachedcredentials
  WHERE  DATEDIFF(MINUTE, cachedate, GETDATE()) >= 29

  -- query the cache
  SELECT  @is_sysadmin = is_sysadmin_member,
          @has_server_access = has_server_access,
          @cachedate = cachedate
  FROM    msdb.dbo.syscachedcredentials
  WHERE   login_name = @login_name
  AND     DATEDIFF(MINUTE, cachedate, GETDATE()) < 29

  IF (@cachedate IS NOT NULL)
  BEGIN
    -- no output variable
    IF (@is_sysadmin_member IS NULL)
    BEGIN
      -- Return result row
      SELECT has_server_access = @has_server_access,
             is_sysadmin       = @is_sysadmin,
             actual_login_name = @login_name
      RETURN
    END
    ELSE
    BEGIN
      SELECT @is_sysadmin_member = @is_sysadmin
      RETURN
    END
  END -- select from cache

  CREATE TABLE #xp_results
  (
  account_name      sysname      NOT NULL PRIMARY KEY,
  type              NVARCHAR(10) NOT NULL,
  privilege         NVARCHAR(10) NOT NULL,
  mapped_login_name sysname      NOT NULL,
  permission_path   sysname      NULL
  )

  -- Set defaults
  SELECT @has_server_access = 0
  SELECT @is_sysadmin = 0
  SELECT @actual_login_name = FORMATMESSAGE(14205)

  IF (@login_name IS NULL)
  BEGIN
    SELECT has_server_access = 1,
           is_sysadmin       = IS_SRVROLEMEMBER(N'sysadmin'),
           actual_login_name = SUSER_SNAME()
    RETURN
  END

  IF (@login_name LIKE '%\%')
  BEGIN
    -- Handle the LocalSystem account ('NT AUTHORITY\SYSTEM') as a special case
    IF (UPPER(@login_name) = N'NT AUTHORITY\SYSTEM')
    BEGIN
      IF (EXISTS (SELECT *
                  FROM master.dbo.syslogins
                  WHERE (UPPER(loginname) = N'BUILTIN\ADMINISTRATORS')))
      BEGIN
        SELECT @has_server_access = hasaccess,
               @is_sysadmin = sysadmin,
               @actual_login_name = loginname
        FROM master.dbo.syslogins
        WHERE (UPPER(loginname) = N'BUILTIN\ADMINISTRATORS')
      END
    END
    ELSE
    BEGIN
      -- Check if the NT login has been explicitly denied access
      IF (EXISTS (SELECT *
                  FROM master.dbo.syslogins
                  WHERE (loginname = @login_name)
                    AND (denylogin = 1)))
      BEGIN
        SELECT @has_server_access = 0,
               @is_sysadmin = sysadmin,
               @actual_login_name = loginname
        FROM master.dbo.syslogins
        WHERE (loginname = @login_name)
      END
      ELSE
      BEGIN
        -- Call xp_logininfo to determine server access
        INSERT INTO #xp_results
        EXECUTE master.dbo.xp_logininfo @login_name

        SELECT @has_server_access = CASE COUNT(*)
                                      WHEN 0 THEN 0
                                      ELSE 1
                                    END
        FROM #xp_results
        SELECT @actual_login_name = mapped_login_name,
               @is_sysadmin = CASE UPPER(privilege)
                                WHEN 'ADMIN' THEN 1
                                ELSE 0
                             END
        FROM #xp_results
      END
    END
  END
  ELSE
  BEGIN
    -- Standard login
    IF (EXISTS (SELECT *
                FROM master.dbo.syslogins
                WHERE (loginname = @login_name)))
    BEGIN
      SELECT @has_server_access = hasaccess,
             @is_sysadmin = sysadmin,
             @actual_login_name = loginname
      FROM master.dbo.syslogins
      WHERE (loginname = @login_name)
    END
  END

  -- update the cache only if something is found
  IF  (UPPER(@actual_login_name) <> '(UNKNOWN)')
  BEGIN
    IF EXISTS (SELECT * FROM msdb.dbo.syscachedcredentials WHERE login_name = @login_name)
    BEGIN
      UPDATE msdb.dbo.syscachedcredentials
      SET    has_server_access = @has_server_access,
             is_sysadmin_member = @is_sysadmin,
             cachedate = GETDATE()
      WHERE  login_name = @login_name
    END
    ELSE
    BEGIN
      INSERT INTO msdb.dbo.syscachedcredentials(login_name, has_server_access, is_sysadmin_member)
      VALUES(@login_name, @has_server_access, @is_sysadmin)
    END
  END

  IF (@is_sysadmin_member IS NULL)
    -- Return result row
    SELECT has_server_access = @has_server_access,
           is_sysadmin       = @is_sysadmin,
           actual_login_name = @actual_login_name
  ELSE
    -- output variable only
    SELECT @is_sysadmin_member = @is_sysadmin
END
go

/**************************************************************/
/* SP_ENUM_SQLAGENT_SUBSYSTEMS                                */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_enum_sqlagent_subsystems...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_enum_sqlagent_subsystems')
              AND (type = 'P')))
  DROP PROCEDURE sp_enum_sqlagent_subsystems
go
CREATE PROCEDURE sp_enum_sqlagent_subsystems
AS
BEGIN
  DECLARE @part                  NVARCHAR(300)
  DECLARE @fmt                   NVARCHAR(300)
  DECLARE @subsystem             NVARCHAR(40)
  DECLARE @replication_installed INT

  SET NOCOUNT ON

  CREATE TABLE #xp_results (subsystem   NVARCHAR(40)  NOT NULL,
                            description NVARCHAR(300) NOT NULL)
  CREATE TABLE #sp_enum_ss_temp (subsystem          NVARCHAR(40) NOT NULL,
                                 description        NVARCHAR(80) NOT NULL,
                                 subsystem_dll      NVARCHAR(80) NULL,
                                 agent_exe          NVARCHAR(80) NULL,
                                 start_entry_point  NVARCHAR(30) NULL,
                                 event_entry_point  NVARCHAR(30) NULL,
                                 stop_entry_point   NVARCHAR(30) NULL,
                                 max_worker_threads INT          NULL)

  -- Check if replication is installed
  EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                N'SOFTWARE\Microsoft\MSSQLServer\Replication',
                                N'IsInstalled',
                                 @replication_installed OUTPUT,
                                N'no_output'
  SELECT @replication_installed = ISNULL(@replication_installed, 0)

  INSERT INTO #xp_results
  EXECUTE master.dbo.xp_regenumvalues N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent\SubSystems'

  IF (@replication_installed = 0)
  BEGIN
    DELETE FROM #xp_results
    WHERE (subsystem IN (N'Distribution', N'LogReader', N'Merge', N'Snapshot'))
  END

  DECLARE all_subsystems CURSOR LOCAL
  FOR
  SELECT subsystem, description
  FROM #xp_results

  OPEN all_subsystems
  FETCH NEXT FROM all_subsystems INTO @subsystem, @part
  WHILE (@@fetch_status = 0)
  BEGIN
    IF (@subsystem = N'TSQL')
      INSERT INTO #sp_enum_ss_temp VALUES (N'TSQL', FORMATMESSAGE(14556), FORMATMESSAGE(14557), FORMATMESSAGE(14557), FORMATMESSAGE(14557), FORMATMESSAGE(14557), FORMATMESSAGE(14557), CONVERT(INT, @part))
    ELSE
    BEGIN
      SELECT @fmt = N''
      WHILE (CHARINDEX(N',', @part) > 0)
      BEGIN
        SELECT @fmt = @fmt + 'N''' + SUBSTRING(@part, 1, CHARINDEX(N',', @part) - 1) + ''', '
        SELECT @part = RIGHT(@part, (DATALENGTH(@part) / 2) - CHARINDEX(N',', @part))
      END
      SELECT @fmt = @fmt + @part
      IF (DATALENGTH(@fmt) > 0)
        INSERT INTO #sp_enum_ss_temp
        EXECUTE(N'SELECT ''' + @subsystem + N''', N'''', ' + @fmt)
    END
    FETCH NEXT FROM all_subsystems INTO @subsystem, @part
  END
  DEALLOCATE all_subsystems

  UPDATE #sp_enum_ss_temp SET description = FORMATMESSAGE(14550)
  WHERE (subsystem = N'CmdExec')
  UPDATE #sp_enum_ss_temp SET description = FORMATMESSAGE(14551)
  WHERE (subsystem = N'Snapshot')
  UPDATE #sp_enum_ss_temp SET description = FORMATMESSAGE(14552)
  WHERE (subsystem = N'LogReader')
  UPDATE #sp_enum_ss_temp SET description = FORMATMESSAGE(14553)
  WHERE (subsystem = N'Distribution')
  UPDATE #sp_enum_ss_temp SET description = FORMATMESSAGE(14554)
  WHERE (subsystem = N'Merge')
  UPDATE #sp_enum_ss_temp SET description = FORMATMESSAGE(14555)
  WHERE (subsystem = N'ActiveScripting')

  -- 'TSQL' is always available (since it's a built-in subsystem), so we explicity add it
  -- to the result set
  IF (NOT EXISTS (SELECT *
                  FROM #sp_enum_ss_temp
                  WHERE (subsystem = N'TSQL')))
    INSERT INTO #sp_enum_ss_temp VALUES (N'TSQL', FORMATMESSAGE(14556), FORMATMESSAGE(14557), FORMATMESSAGE(14557), FORMATMESSAGE(14557), FORMATMESSAGE(14557), FORMATMESSAGE(14557), CASE (PLATFORM() & 0x2) WHEN 0x2 THEN 10 ELSE 20 END) -- Worker thread rule should match DEF_REG_MAX_TSQL_WORKER_THREADS

  SELECT subsystem,
         description,
         subsystem_dll,
         agent_exe,
         start_entry_point,
         event_entry_point,
         stop_entry_point,
         max_worker_threads
  FROM #sp_enum_ss_temp
  ORDER BY subsystem
END
go

/**************************************************************/
/* SP_GET_SQLAGENT_PROPERTIES                                 */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_get_sqlagent_properties...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_get_sqlagent_properties')
              AND (type = 'P')))
  DROP PROCEDURE sp_get_sqlagent_properties
go
CREATE PROCEDURE sp_get_sqlagent_properties
AS
BEGIN
  DECLARE @auto_start                  INT
  DECLARE @startup_account             NVARCHAR(100)
  DECLARE @msx_server_name             NVARCHAR(30)

  -- Non-SQLDMO exposed properties
  DECLARE @sqlserver_restart           INT
  DECLARE @jobhistory_max_rows         INT
  DECLARE @jobhistory_max_rows_per_job INT
  DECLARE @errorlog_file               NVARCHAR(255)
  DECLARE @errorlogging_level          INT
  DECLARE @error_recipient             NVARCHAR(30)
  DECLARE @monitor_autostart           INT
  DECLARE @local_host_server           NVARCHAR(30)
  DECLARE @job_shutdown_timeout        INT
  DECLARE @cmdexec_account             VARBINARY(64)
  DECLARE @regular_connections         INT
  DECLARE @host_login_name             sysname
  DECLARE @host_login_password         VARBINARY(512)
  DECLARE @login_timeout               INT
  DECLARE @idle_cpu_percent            INT
  DECLARE @idle_cpu_duration           INT
  DECLARE @oem_errorlog                INT
  DECLARE @sysadmin_only               INT
  DECLARE @email_profile               NVARCHAR(64)
  DECLARE @email_save_in_sent_folder   INT
  DECLARE @cpu_poller_enabled          INT

  SET NOCOUNT ON

  -- NOTE: We return all SQLServerAgent properties at one go for performance reasons

  -- Read the values from the registry
  IF ((PLATFORM() & 0x1) = 0x1) -- NT
  BEGIN
    EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                  N'SYSTEM\CurrentControlSet\Services\SQLServerAgent',
                                  N'Start',
                                   @auto_start OUTPUT,
                                  N'no_output'
    EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                  N'SYSTEM\CurrentControlSet\Services\SQLServerAgent',
                                  N'ObjectName',
                                   @startup_account OUTPUT,
                                  N'no_output'
  END
  ELSE
  BEGIN
    SELECT @auto_start = 3 -- Manual start
    SELECT @startup_account = NULL
  END
  EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                N'MSXServerName',
                                 @msx_server_name OUTPUT,
                                N'no_output'

  -- Non-SQLDMO exposed properties
  EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                N'RestartSQLServer',
                                 @sqlserver_restart OUTPUT,
                                N'no_output'
  EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                N'JobHistoryMaxRows',
                                 @jobhistory_max_rows OUTPUT,
                                N'no_output'
  EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                N'JobHistoryMaxRowsPerJob',
                                 @jobhistory_max_rows_per_job OUTPUT,
                                N'no_output'
  EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                N'ErrorLogFile',
                                 @errorlog_file OUTPUT,
                                N'no_output'
  EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                N'ErrorLoggingLevel',
                                 @errorlogging_level OUTPUT,
                                N'no_output'
  EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                N'ErrorMonitor',
                                 @error_recipient OUTPUT,
                                N'no_output'
  EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                N'MonitorAutoStart',
                                 @monitor_autostart OUTPUT,
                                N'no_output'
  EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                N'ServerHost',
                                 @local_host_server OUTPUT,
                                N'no_output'
  EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                N'JobShutdownTimeout',
                                 @job_shutdown_timeout OUTPUT,
                                N'no_output'
  EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                N'CmdExecAccount',
                                 @cmdexec_account OUTPUT,
                                N'no_output'
  EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                N'RegularConnections',
                                 @regular_connections OUTPUT,
                                N'no_output'
  EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                N'HostLoginID',
                                 @host_login_name OUTPUT,
                                N'no_output'
  EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                N'HostPassword',
                                 @host_login_password OUTPUT,
                                N'no_output'
  EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                N'LoginTimeout',
                                 @login_timeout OUTPUT,
                                N'no_output'
  EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                N'IdleCPUPercent',
                                 @idle_cpu_percent OUTPUT,
                                N'no_output'
  EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                N'IdleCPUDuration',
                                 @idle_cpu_duration OUTPUT,
                                N'no_output'
  EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                N'OemErrorLog',
                                 @oem_errorlog OUTPUT,
                                N'no_output'
  EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                N'SysAdminOnly',
                                 @sysadmin_only OUTPUT,
                                N'no_output'
  EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                N'EmailProfile',
                                 @email_profile OUTPUT,
                                N'no_output'
  EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                N'EmailSaveSent',
                                 @email_save_in_sent_folder OUTPUT,
                                N'no_output'
  EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                N'CoreEngineMask',
                                 @cpu_poller_enabled OUTPUT,
                                N'no_output'
  IF (@cpu_poller_enabled IS NOT NULL)
    SELECT @cpu_poller_enabled = CASE WHEN (@cpu_poller_enabled & 32) = 32 THEN 0 ELSE 1 END

  -- Return the values to the client
  SELECT auto_start = CASE @auto_start
                        WHEN 2 THEN 1 -- 2 means auto-start
                        WHEN 3 THEN 0 -- 3 means don't auto-start
                        ELSE 0        -- Safety net
                      END,
         msx_server_name = @msx_server_name,
         sqlagent_type = (SELECT CASE
                                    WHEN (COUNT(*) = 0) AND (ISNULL(DATALENGTH(@msx_server_name), 0) = 0) THEN 1 -- Standalone
                                    WHEN (COUNT(*) = 0) AND (ISNULL(DATALENGTH(@msx_server_name), 0) > 0) THEN 2 -- TSX
                                    WHEN (COUNT(*) > 0) AND (ISNULL(DATALENGTH(@msx_server_name), 0) = 0) THEN 3 -- MSX
                                    WHEN (COUNT(*) > 0) AND (ISNULL(DATALENGTH(@msx_server_name), 0) > 0) THEN 0 -- Multi-Level MSX (currently invalid)
                                    ELSE 0 -- Invalid
                                  END
                           FROM msdb.dbo.systargetservers),
         startup_account = @startup_account,

         -- Non-SQLDMO exposed properties
         sqlserver_restart = @sqlserver_restart,
         jobhistory_max_rows = @jobhistory_max_rows,
         jobhistory_max_rows_per_job = @jobhistory_max_rows_per_job,
         errorlog_file = @errorlog_file,
         errorlogging_level = ISNULL(@errorlogging_level, 7),
         error_recipient = @error_recipient,
         monitor_autostart = ISNULL(@monitor_autostart, 0),
         local_host_server = @local_host_server,
         job_shutdown_timeout = ISNULL(@job_shutdown_timeout, 15),
         cmdexec_account = @cmdexec_account,
         regular_connections = ISNULL(@regular_connections, 0),
         host_login_name = @host_login_name,
         host_login_password = @host_login_password,
         login_timeout = ISNULL(@login_timeout, 30),
         idle_cpu_percent = ISNULL(@idle_cpu_percent, 10),
         idle_cpu_duration = ISNULL(@idle_cpu_duration, 600),
         oem_errorlog = ISNULL(@oem_errorlog, 0),
         sysadmin_only = ISNULL(@sysadmin_only, 0),
         email_profile = @email_profile,
         email_save_in_sent_folder = ISNULL(@email_save_in_sent_folder, 0),
         cpu_poller_enabled = ISNULL(@cpu_poller_enabled, 0)
END
go

/**************************************************************/
/* SP_SET_SQLAGENT_PROPERTIES                                 */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_set_sqlagent_properties...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_set_sqlagent_properties')
              AND (type = 'P')))
  DROP PROCEDURE sp_set_sqlagent_properties
go
CREATE PROCEDURE sp_set_sqlagent_properties
  @auto_start                  INT           = NULL, -- 1 or 0

  -- Non-SQLDMO exposed properties
  @sqlserver_restart           INT           = NULL, -- 1 or 0
  @jobhistory_max_rows         INT           = NULL, -- No maximum = -1, otherwise must be > 1
  @jobhistory_max_rows_per_job INT           = NULL, -- 1 to @jobhistory_max_rows
  @errorlog_file               NVARCHAR(255) = NULL, -- Full drive\path\name of errorlog file
  @errorlogging_level          INT           = NULL, -- 1 = error, 2 = warning, 4 = information
  @error_recipient             NVARCHAR(30)  = NULL, -- Network address of error popup recipient
  @monitor_autostart           INT           = NULL, -- 1 or 0
  @local_host_server           NVARCHAR(30)  = NULL, -- Alias of local host server
  @job_shutdown_timeout        INT           = NULL, -- 5 to 600 seconds
  @cmdexec_account             VARBINARY(64) = NULL, -- CmdExec account information
  @regular_connections         INT           = NULL, -- 1 or 0
  @host_login_name             sysname       = NULL, -- Login name (if regular_connections = 1)
  @host_login_password         VARBINARY(512)= NULL, -- Login password (if regular_connections = 1)
  @login_timeout               INT           = NULL, -- 5 to 45 (seconds)
  @idle_cpu_percent            INT           = NULL, -- 1 to 100
  @idle_cpu_duration           INT           = NULL, -- 20 to 86400 seconds
  @oem_errorlog                INT           = NULL, -- 1 or 0
  @sysadmin_only               INT           = NULL, -- 1 or 0
  @email_profile               NVARCHAR(64)  = NULL, -- Email profile name
  @email_save_in_sent_folder   INT           = NULL, -- 1 or 0
  @cpu_poller_enabled          INT           = NULL  -- 1 or 0
AS
BEGIN
  -- NOTE: We set all SQLServerAgent properties at one go for performance reasons.
  -- NOTE: You cannot set the value of the properties msx_server_name, is_msx or
  --       startup_account - they are all read only.

  DECLARE @res_valid_range           NVARCHAR(100)
  DECLARE @existing_core_engine_mask INT

  SET NOCOUNT ON

  -- Remove any leading/trailing spaces from parameters
  SELECT @errorlog_file     = LTRIM(RTRIM(@errorlog_file))
  SELECT @error_recipient   = LTRIM(RTRIM(@error_recipient))
  SELECT @local_host_server = LTRIM(RTRIM(@local_host_server))
  SELECT @host_login_name   = LTRIM(RTRIM(@host_login_name))
  SELECT @email_profile     = LTRIM(RTRIM(@email_profile))

  -- Make sure values (if supplied) are good
  IF (@auto_start IS NOT NULL)
  BEGIN
    -- NOTE: When setting the the services start value, 2 == auto-start, 3 == Don't auto-start
    SELECT @auto_start = CASE @auto_start
                           WHEN 0 THEN 3
                           WHEN 1 THEN 2
                           ELSE 3 -- Assume non auto-start if passed a junk value
                          END
  END

  -- Non-SQLDMO exposed properties
  IF ((@sqlserver_restart IS NOT NULL) AND (@sqlserver_restart <> 0))
    SELECT @sqlserver_restart = 1

  IF (@jobhistory_max_rows IS NOT NULL)
  BEGIN
    SELECT @res_valid_range = FORMATMESSAGE(14207)
    IF ((@jobhistory_max_rows < -1) OR (@jobhistory_max_rows = 0))
    BEGIN
      RAISERROR(14266, -1, -1, '@jobhistory_max_rows', @res_valid_range)
      RETURN(1) -- Failure
    END
  END
  ELSE
  BEGIN
    EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                  N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                  N'JobHistoryMaxRows',
                                   @jobhistory_max_rows OUTPUT,
                                  N'no_output'
    SELECT @jobhistory_max_rows = ISNULL(@jobhistory_max_rows, -1)
  END

  IF (@jobhistory_max_rows_per_job IS NOT NULL)
  BEGIN
    IF (@jobhistory_max_rows = -1)
      SELECT @jobhistory_max_rows_per_job = 0
    ELSE
    BEGIN
      IF ((@jobhistory_max_rows_per_job < 1) OR (@jobhistory_max_rows_per_job > @jobhistory_max_rows))
      BEGIN
        SELECT @res_valid_range = N'1..' + CONVERT(NVARCHAR, @jobhistory_max_rows)
        RAISERROR(14266, -1, -1, '@jobhistory_max_rows', @res_valid_range)
        RETURN(1) -- Failure
      END
    END
  END

  IF (@errorlogging_level IS NOT NULL) AND ((@errorlogging_level < 1) OR (@errorlogging_level > 7))
  BEGIN
    RAISERROR(14266, -1, -1, '@errorlogging_level', '1..7')
    RETURN(1) -- Failure
  END

  IF (@monitor_autostart IS NOT NULL) AND ((@monitor_autostart < 0) OR (@monitor_autostart > 1))
  BEGIN
    RAISERROR(14266, -1, -1, '@monitor_autostart', '0, 1')
    RETURN(1) -- Failure
  END

  IF (@job_shutdown_timeout IS NOT NULL) AND ((@job_shutdown_timeout < 5) OR (@job_shutdown_timeout > 600))
  BEGIN
    RAISERROR(14266, -1, -1, '@job_shutdown_timeout', '5..600')
    RETURN(1) -- Failure
  END

  IF (@regular_connections IS NOT NULL) AND ((@regular_connections < 0) OR (@regular_connections > 1))
  BEGIN
    RAISERROR(14266, -1, -1, '@regular_connections', '0, 1')
    RETURN(1) -- Failure
  END

  IF (@login_timeout IS NOT NULL) AND ((@login_timeout < 5) OR (@login_timeout > 45))
  BEGIN
    RAISERROR(14266, -1, -1, '@login_timeout', '5..45')
    RETURN(1) -- Failure
  END

  IF ((@idle_cpu_percent IS NOT NULL) AND ((@idle_cpu_percent < 1) OR (@idle_cpu_percent > 100)))
  BEGIN
    RAISERROR(14266, -1, -1, '@idle_cpu_percent', '10..100')
    RETURN(1) -- Failure
  END

  IF ((@idle_cpu_duration IS NOT NULL) AND ((@idle_cpu_duration < 20) OR (@idle_cpu_duration > 86400)))
  BEGIN
    RAISERROR(14266, -1, -1, '@idle_cpu_duration', '20..86400')
    RETURN(1) -- Failure
  END

  IF (@oem_errorlog IS NOT NULL) AND ((@oem_errorlog < 0) OR (@oem_errorlog > 1))
  BEGIN
    RAISERROR(14266, -1, -1, '@oem_errorlog', '0, 1')
    RETURN(1) -- Failure
  END

  IF (@sysadmin_only IS NOT NULL) AND ((@sysadmin_only < 0) OR (@sysadmin_only > 1))
  BEGIN
    RAISERROR(14266, -1, -1, '@sysadmin_only', '0, 1')
    RETURN(1) -- Failure
  END

  IF (@email_save_in_sent_folder IS NOT NULL) AND ((@email_save_in_sent_folder < 0) OR (@email_save_in_sent_folder > 1))
  BEGIN
    RAISERROR(14266, -1, -1, 'email_save_in_sent_folder', '0, 1')
    RETURN(1) -- Failure
  END

  IF (@cpu_poller_enabled IS NOT NULL) AND ((@cpu_poller_enabled < 0) OR (@cpu_poller_enabled > 1))
  BEGIN
    RAISERROR(14266, -1, -1, 'cpu_poller_enabled', '0, 1')
    RETURN(1) -- Failure
  END

  -- Write out the values
  IF (@auto_start IS NOT NULL)
  BEGIN
    IF ((PLATFORM() & 0x1) = 0x1) -- NT
      EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',
                                     N'SYSTEM\CurrentControlSet\Services\SQLServerAgent',
                                     N'Start',
                                     N'REG_DWORD',
                                      @auto_start
    ELSE
      RAISERROR(14546, 16, 1, '@auto_start')
  END

  -- Non-SQLDMO exposed properties
  IF (@sqlserver_restart IS NOT NULL)
    EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',
                                   N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                   N'RestartSQLServer',
                                   N'REG_DWORD',
                                    @sqlserver_restart
  IF (@jobhistory_max_rows IS NOT NULL)
    EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',
                                   N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                   N'JobHistoryMaxRows',
                                   N'REG_DWORD',
                                    @jobhistory_max_rows
  IF (@jobhistory_max_rows_per_job IS NOT NULL)
    EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',
                                   N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                   N'JobHistoryMaxRowsPerJob',
                                   N'REG_DWORD',
                                    @jobhistory_max_rows_per_job
  IF (@errorlog_file IS NOT NULL)
    EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',
                                   N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                   N'ErrorLogFile',
                                   N'REG_SZ',
                                    @errorlog_file
  IF (@errorlogging_level IS NOT NULL)
    EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',
                                   N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                   N'ErrorLoggingLevel',
                                   N'REG_DWORD',
                                    @errorlogging_level
  IF (@error_recipient IS NOT NULL)
    EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',
                                   N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                   N'ErrorMonitor',
                                   N'REG_SZ',
                                    @error_recipient
  IF (@monitor_autostart IS NOT NULL)
    EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',
                                   N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                   N'MonitorAutoStart',
                                   N'REG_DWORD',
                                    @monitor_autostart
  IF (@local_host_server IS NOT NULL)
    EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',
                                   N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                   N'ServerHost',
                                   N'REG_SZ',
                                    @local_host_server
  IF (@job_shutdown_timeout IS NOT NULL)
    EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',
                                   N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                   N'JobShutdownTimeout',
                                   N'REG_DWORD',
                                    @job_shutdown_timeout
  IF (@cmdexec_account IS NOT NULL)
    EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',
                                   N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                   N'CmdExecAccount',
                                   N'REG_BINARY',
                                    @cmdexec_account
  IF (@regular_connections IS NOT NULL)
    EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',
                                   N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                   N'RegularConnections',
                                   N'REG_DWORD',
                                    @regular_connections
  IF (@host_login_name IS NOT NULL)
    EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',
                                   N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                   N'HostLoginID',
                                   N'REG_SZ',
                                    @host_login_name
  IF (@host_login_password IS NOT NULL)
    EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',
                                   N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                   N'HostPassword',
                                   N'REG_BINARY',
                                    @host_login_password
  IF (@login_timeout IS NOT NULL)
    EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',
                                   N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                   N'LoginTimeout',
                                   N'REG_DWORD',
                                    @login_timeout
  IF (@idle_cpu_percent IS NOT NULL)
    EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',
                                   N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                   N'IdleCPUPercent',
                                   N'REG_DWORD',
                                    @idle_cpu_percent
  IF (@idle_cpu_duration IS NOT NULL)
    EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',
                                   N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                   N'IdleCPUDuration',
                                   N'REG_DWORD',
                                    @idle_cpu_duration
  IF (@oem_errorlog IS NOT NULL)
    EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',
                                   N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                   N'OemErrorLog',
                                   N'REG_DWORD',
                                    @oem_errorlog
  IF (@sysadmin_only IS NOT NULL)
    EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',
                                   N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                   N'SysAdminOnly',
                                   N'REG_DWORD',
                                    @sysadmin_only
  IF (@email_profile IS NOT NULL)
    EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',
                                   N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                   N'EmailProfile',
                                   N'REG_SZ',
                                    @email_profile
  IF (@email_save_in_sent_folder IS NOT NULL)
    EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',
                                   N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                   N'EmailSaveSent',
                                   N'REG_DWORD',
                                    @email_save_in_sent_folder
  IF (@cpu_poller_enabled IS NOT NULL)
  BEGIN
    EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                  N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                  N'CoreEngineMask',
                                   @existing_core_engine_mask OUTPUT,
                                  N'no_output'
    IF ((@existing_core_engine_mask IS NOT NULL) OR (@cpu_poller_enabled = 1))
    BEGIN
      IF (@cpu_poller_enabled = 1)
        SELECT @cpu_poller_enabled = (ISNULL(@existing_core_engine_mask, 0) & ~32)
      ELSE
        SELECT @cpu_poller_enabled = (ISNULL(@existing_core_engine_mask, 0) | 32)

      IF ((@existing_core_engine_mask IS NOT NULL) AND (@cpu_poller_enabled = 32))
        EXECUTE master.dbo.xp_regdeletevalue N'HKEY_LOCAL_MACHINE',
                                             N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                             N'CoreEngineMask'
      ELSE
        EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',
                                       N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                       N'CoreEngineMask',
                                       N'REG_DWORD',
                                        @cpu_poller_enabled
    END
  END

  RETURN(0) -- Success
END
go

/**************************************************************/
/* SP_MSX_ENLIST                                              */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_msx_enlist...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = 'sp_msx_enlist')
              AND (type = 'P')))
  DROP PROCEDURE sp_msx_enlist
go
CREATE PROCEDURE sp_msx_enlist
  @msx_server_name NVARCHAR(30),
  @location        NVARCHAR(100) = NULL, -- The procedure will supply a default
  @ping_server     BIT = 1               -- Set to 0 to skip the MSX ping test
AS
BEGIN
  DECLARE @current_msx_server   NVARCHAR(30)
  DECLARE @local_machine_name   NVARCHAR(30)
  DECLARE @retval               INT
  DECLARE @time_zone_adjustment INT
  DECLARE @local_time           NVARCHAR(100)
  DECLARE @nt_user              NVARCHAR(100)
  DECLARE @poll_interval        INT

  SET NOCOUNT ON

  -- Only a sysadmin can do this
  IF (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1)
  BEGIN
    RAISERROR(15003, 16, 1, N'sysadmin')
    RETURN(1) -- Failure
  END

  -- Only an NT server can be enlisted
  IF ((PLATFORM() & 0x1) <> 0x1) -- NT
  BEGIN
    RAISERROR(14540, -1, 1)
    RETURN(1) -- Failure
  END

  -- Only SBS, Standard, or Enterprise editions of SQL Server can be enlisted
  IF ((PLATFORM() & 0x100) = 0x100) -- Desktop package
  BEGIN
    RAISERROR(14539, -1, -1)
    RETURN(1) -- Failure
  END

  -- Remove any leading/trailing spaces from parameters
  SELECT @msx_server_name = LTRIM(RTRIM(@msx_server_name))
  SELECT @location        = LTRIM(RTRIM(@location))

  -- Turn [nullable] empty string parameters into NULLs
  IF (@location = N'') SELECT @location = NULL

  -- Change to MSX server name to upper-case since it's a machine name
  SELECT @msx_server_name = UPPER(@msx_server_name)

  SELECT @retval = 0

  -- Get the values that we'll need for the [re]enlistment operation (except the local time
  -- which we get right before we call xp_msx_enlist to that it's as accurate as possible)
  SELECT @nt_user = ISNULL(NT_CLIENT(), ISNULL(SUSER_SNAME(), FORMATMESSAGE(14205)))
  EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                N'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
                                N'Bias',
                                 @time_zone_adjustment OUTPUT,
                                N'no_output'
  IF ((PLATFORM() & 0x1) = 0x1) -- NT
    SELECT @time_zone_adjustment = -ISNULL(@time_zone_adjustment, 0)
  ELSE
    SELECT @time_zone_adjustment = -CONVERT(INT, CONVERT(BINARY(2), ISNULL(@time_zone_adjustment, 0)))

  EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                N'MSXPollInterval',
                                 @poll_interval OUTPUT,
                                N'no_output'
  SELECT @poll_interval = ISNULL(@poll_interval, 60) -- This should be the same as DEF_REG_MSX_POLL_INTERVAL
  EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                N'MSXServerName',
                                 @current_msx_server OUTPUT,
                                N'no_output'
  SELECT @current_msx_server = LTRIM(RTRIM(@current_msx_server))
  EXECUTE @retval = master.dbo.xp_getnetname @local_machine_name OUTPUT
  IF (@retval <> 0)
    RETURN(1) -- Failure

  -- Check if this machine is an MSX (and therefore cannot be enlisted into another MSX)
  IF (EXISTS (SELECT *
              FROM msdb.dbo.systargetservers))
  BEGIN
    RAISERROR(14299, -1, -1, @local_machine_name)
    RETURN(1) -- Failure
  END

  -- Check if the MSX supplied is the same as the local machine (this is not allowed)
  IF (UPPER(@local_machine_name) = UPPER(@msx_server_name))
  BEGIN
    RAISERROR(14297, -1, -1)
    RETURN(1) -- Failure
  END

  -- Check if MSDB has be re-installed since we enlisted
  IF (@current_msx_server IS NOT NULL) AND
     (NOT EXISTS (SELECT *
                  FROM msdb.dbo.sqlagent_info
                  WHERE (attribute = 'DateEnlisted')))
  BEGIN
    -- User is tring to [re]enlist after a re-install, so we have to forcefully defect before
    -- we can fully enlist again
    EXECUTE msdb.dbo.sp_msx_defect @forced_defection = 1
    SELECT @current_msx_server = NULL
  END

  -- Check if we are already enlisted, in which case we re-enlist
  IF ((@current_msx_server IS NOT NULL) AND (@current_msx_server <> N''))
  BEGIN
    IF (UPPER(@current_msx_server) = UPPER(@msx_server_name))
    BEGIN
      -- Update the [existing] enlistment
      SELECT @local_time = CONVERT(NVARCHAR, GETDATE(), 112) + N' ' + CONVERT(NVARCHAR, GETDATE(), 108)
      EXECUTE @retval = master.dbo.xp_msx_enlist 2, @msx_server_name, @nt_user, @location, @time_zone_adjustment, @local_time, @poll_interval
      RETURN(@retval) -- 0 means success
    END
    ELSE
    BEGIN
      RAISERROR(14296, -1, -1, @current_msx_server)
      RETURN(1) -- Failure
    END
  END

  -- If we get this far then we're dealing with a new enlistment...

  -- Check if the MSX supplied exists on the network
  IF (@ping_server = 1)
  BEGIN
    DECLARE @msx_machine_name NVARCHAR (30)
    DECLARE @char_index       INT

    SELECT @char_index = CHARINDEX (N'\', @msx_server_name)
    IF (@char_index > 0)
    BEGIN
      SELECT @msx_machine_name = LEFT (@msx_server_name, @char_index - 1)
    END
    ELSE
    BEGIN
      SELECT @msx_machine_name = @msx_server_name
    END

    IF ((PLATFORM() & 0x2) = 0x2) -- Win9x
    BEGIN
      EXECUTE(N'CREATE TABLE #output (output NVARCHAR(1024) COLLATE database_default)
                SET NOCOUNT ON
                INSERT INTO #output
                EXECUTE master.dbo.xp_cmdshell N''net view \\' + @msx_machine_name + N'''
                IF (EXISTS (SELECT *
                            FROM #output
                            WHERE (output LIKE N''% 53%'')))
                   RAISERROR(14262, -1, -1, N''@msx_server_name'', N''' + @msx_machine_name + N''') WITH SETERROR')
      IF (@@error <> 0)
        RETURN(1) -- Failure
    END
    ELSE
    BEGIN
      EXECUTE(N'DECLARE @retval INT
                SET NOCOUNT ON
                EXECUTE @retval = master.dbo.xp_cmdshell N''net view \\' + @msx_machine_name + N' > nul'', no_output
                IF (@retval <> 0)
                  RAISERROR(14262, -1, -1, N''@msx_server_name'', N''' + @msx_machine_name + N''') WITH SETERROR')
      IF (@@error <> 0)
        RETURN(1) -- Failure
    END
  END

  -- If no location is supplied, generate one (such as we can)
  IF (@location IS NULL)
    EXECUTE msdb.dbo.sp_generate_server_description @location OUTPUT

  SELECT @local_time = CONVERT(NVARCHAR, GETDATE(), 112) + ' ' + CONVERT(NVARCHAR, GETDATE(), 108)
  EXECUTE @retval = master.dbo.xp_msx_enlist 0, @msx_server_name, @nt_user, @location, @time_zone_adjustment, @local_time, @poll_interval

  IF (@retval = 0)
  BEGIN
    EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',
                                   N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                   N'MSXServerName',
                                   N'REG_SZ',
                                    @msx_server_name

    IF (@current_msx_server IS NOT NULL)
      RAISERROR(14228, 0, 1, @current_msx_server, @msx_server_name)
    ELSE
      RAISERROR(14229, 0, 1, @msx_server_name)

    -- Add entry to sqlagent_info
    INSERT INTO msdb.dbo.sqlagent_info (attribute, value) VALUES ('DateEnlisted', CONVERT(VARCHAR(10), GETDATE(), 112))
  END

  RETURN(@retval) -- 0 means success
END
go


/**************************************************************/
/* SP_GET_COMPOSITE_JOB_INFO                                  */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_get_composite_job_info...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_get_composite_job_info')
              AND (type = 'P')))
  DROP PROCEDURE sp_get_composite_job_info
go
CREATE PROCEDURE sp_get_composite_job_info
  @job_id             UNIQUEIDENTIFIER = NULL,
  @job_type           VARCHAR(12)      = NULL,  -- LOCAL or MULTI-SERVER
  @owner_login_name   sysname          = NULL,
  @subsystem          NVARCHAR(40)     = NULL,
  @category_id        INT              = NULL,
  @enabled            TINYINT          = NULL,
  @execution_status   INT              = NULL,  -- 0 = Not idle or suspended, 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, [6 = WaitingForStepToFinish], 7 = PerformingCompletionActions
  @date_comparator    CHAR(1)          = NULL,  -- >, < or =
  @date_created       DATETIME         = NULL,
  @date_last_modified DATETIME         = NULL,
  @description        NVARCHAR(512)    = NULL   -- We do a LIKE on this so it can include wildcards
AS
BEGIN
  DECLARE @is_sysadmin INT
  DECLARE @job_owner   sysname

  SET NOCOUNT ON

  -- By 'composite' we mean a combination of sysjobs and xp_sqlagent_enum_jobs data.
  -- This proc should only ever be called by sp_help_job, so we don't verify the
  -- parameters (sp_help_job has already done this).

  -- Step 1: Create intermediate work tables
  CREATE TABLE #job_execution_state (job_id                  UNIQUEIDENTIFIER NOT NULL,
                                     date_started            INT              NOT NULL,
                                     time_started            INT              NOT NULL,
                                     execution_job_status    INT              NOT NULL,
                                     execution_step_id       INT              NULL,
                                     execution_step_name     sysname          NULL,
                                     execution_retry_attempt INT              NOT NULL,
                                     next_run_date           INT              NOT NULL,
                                     next_run_time           INT              NOT NULL,
                                     next_run_schedule_id    INT              NOT NULL)
  CREATE TABLE #filtered_jobs (job_id                   UNIQUEIDENTIFIER NOT NULL,
                               date_created             DATETIME         NOT NULL,
                               date_last_modified       DATETIME         NOT NULL,
                               current_execution_status INT              NULL,
                               current_execution_step   sysname          NULL,
                               current_retry_attempt    INT              NULL,
                               last_run_date            INT              NOT NULL,
                               last_run_time            INT              NOT NULL,
                               last_run_outcome         INT              NOT NULL,
                               next_run_date            INT              NULL,
                               next_run_time            INT              NULL,
                               next_run_schedule_id     INT              NULL,
                               type                     INT              NOT NULL)
  CREATE TABLE #xp_results (job_id                UNIQUEIDENTIFIER NOT NULL,
                            last_run_date         INT              NOT NULL,
                            last_run_time         INT              NOT NULL,
                            next_run_date         INT              NOT NULL,
                            next_run_time         INT              NOT NULL,
                            next_run_schedule_id  INT              NOT NULL,
                            requested_to_run      INT              NOT NULL, -- BOOL
                            request_source        INT              NOT NULL,
                            request_source_id     sysname          NULL,
                            running               INT              NOT NULL, -- BOOL
                            current_step          INT              NOT NULL,
                            current_retry_attempt INT              NOT NULL,
                            job_state             INT              NOT NULL)

  -- Step 2: Capture job execution information (for local jobs only since that's all SQLServerAgent caches)
  SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
  SELECT @job_owner = SUSER_SNAME()


  IF ((@@microsoftversion / 0x01000000) >= 8) -- SQL Server 8.0 or greater
    INSERT INTO #xp_results
    EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner, @job_id
  ELSE
    INSERT INTO #xp_results
    EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner

  INSERT INTO #job_execution_state
  SELECT xpr.job_id,
         xpr.last_run_date,
         xpr.last_run_time,
         xpr.job_state,
         sjs.step_id,
         sjs.step_name,
         xpr.current_retry_attempt,
         xpr.next_run_date,
         xpr.next_run_time,
         xpr.next_run_schedule_id
  FROM #xp_results                          xpr
       LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs ON ((xpr.job_id = sjs.job_id) AND (xpr.current_step = sjs.step_id)),
       msdb.dbo.sysjobs_view                sjv
  WHERE (sjv.job_id = xpr.job_id)

  -- Step 3: Filter on everything but dates and job_type
  IF ((@subsystem        IS NULL) AND
      (@owner_login_name IS NULL) AND
      (@enabled          IS NULL) AND
      (@category_id      IS NULL) AND
      (@execution_status IS NULL) AND
      (@description      IS NULL) AND
      (@job_id           IS NULL))
  BEGIN
    -- Optimize for the frequently used case...
    INSERT INTO #filtered_jobs
    SELECT sjv.job_id,
           sjv.date_created,
           sjv.date_modified,
           ISNULL(jes.execution_job_status, 4), -- Will be NULL if the job is non-local or is not in #job_execution_state (NOTE: 4 = STATE_IDLE)
           CASE ISNULL(jes.execution_step_id, 0)
             WHEN 0 THEN NULL                   -- Will be NULL if the job is non-local or is not in #job_execution_state
             ELSE CONVERT(NVARCHAR, jes.execution_step_id) + N' (' + jes.execution_step_name + N')'
           END,
           jes.execution_retry_attempt,         -- Will be NULL if the job is non-local or is not in #job_execution_state
           0,  -- last_run_date placeholder    (we'll fix it up in step 3.3)
           0,  -- last_run_time placeholder    (we'll fix it up in step 3.3)
           5,  -- last_run_outcome placeholder (we'll fix it up in step 3.3 - NOTE: We use 5 just in case there are no jobservers for the job)
           jes.next_run_date,                   -- Will be NULL if the job is non-local or is not in #job_execution_state
           jes.next_run_time,                   -- Will be NULL if the job is non-local or is not in #job_execution_state
           jes.next_run_schedule_id,            -- Will be NULL if the job is non-local or is not in #job_execution_state
           0   -- type placeholder             (we'll fix it up in step 3.4)
    FROM msdb.dbo.sysjobs_view                sjv
         LEFT OUTER JOIN #job_execution_state jes ON (sjv.job_id = jes.job_id)
  END
  ELSE
  BEGIN
    INSERT INTO #filtered_jobs
    SELECT DISTINCT
           sjv.job_id,
           sjv.date_created,
           sjv.date_modified,
           ISNULL(jes.execution_job_status, 4), -- Will be NULL if the job is non-local or is not in #job_execution_state (NOTE: 4 = STATE_IDLE)
           CASE ISNULL(jes.execution_step_id, 0)
             WHEN 0 THEN NULL                   -- Will be NULL if the job is non-local or is not in #job_execution_state
             ELSE CONVERT(NVARCHAR, jes.execution_step_id) + N' (' + jes.execution_step_name + N')'
           END,
           jes.execution_retry_attempt,         -- Will be NULL if the job is non-local or is not in #job_execution_state
           0,  -- last_run_date placeholder    (we'll fix it up in step 3.3)
           0,  -- last_run_time placeholder    (we'll fix it up in step 3.3)
           5,  -- last_run_outcome placeholder (we'll fix it up in step 3.3 - NOTE: We use 5 just in case there are no jobservers for the job)
           jes.next_run_date,                   -- Will be NULL if the job is non-local or is not in #job_execution_state
           jes.next_run_time,                   -- Will be NULL if the job is non-local or is not in #job_execution_state
           jes.next_run_schedule_id,            -- Will be NULL if the job is non-local or is not in #job_execution_state
           0   -- type placeholder             (we'll fix it up in step 3.4)
    FROM msdb.dbo.sysjobs_view                sjv
         LEFT OUTER JOIN #job_execution_state jes ON (sjv.job_id = jes.job_id)
         LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs ON (sjv.job_id = sjs.job_id)
    WHERE ((@subsystem        IS NULL) OR (sjs.subsystem            = @subsystem))
      AND ((@owner_login_name IS NULL) OR (sjv.owner_sid            = SUSER_SID(@owner_login_name)))
      AND ((@enabled          IS NULL) OR (sjv.enabled              = @enabled))
      AND ((@category_id      IS NULL) OR (sjv.category_id          = @category_id))
      AND ((@execution_status IS NULL) OR ((@execution_status > 0) AND (jes.execution_job_status = @execution_status))
                                       OR ((@execution_status = 0) AND (jes.execution_job_status <> 4) AND (jes.execution_job_status <> 5)))
      AND ((@description      IS NULL) OR (sjv.description       LIKE @description))
      AND ((@job_id           IS NULL) OR (sjv.job_id               = @job_id))
  END

  -- Step 3.1: Change the execution status of non-local jobs from 'Idle' to 'Unknown'
  UPDATE #filtered_jobs
  SET current_execution_status = NULL
  WHERE (current_execution_status = 4)
    AND (job_id IN (SELECT job_id
                    FROM msdb.dbo.sysjobservers
                    WHERE (server_id <> 0)))

  -- Step 3.2: Check that if the user asked to see idle jobs that we still have some.
  --           If we don't have any then the query should return no rows.
  IF (@execution_status = 4) AND
     (NOT EXISTS (SELECT *
                  FROM #filtered_jobs
                  WHERE (current_execution_status = 4)))
  BEGIN
    TRUNCATE TABLE #filtered_jobs
  END

  -- Step 3.3: Populate the last run date/time/outcome [this is a little tricky since for
  --           multi-server jobs there are multiple last run details in sysjobservers, so
  --           we simply choose the most recent].
  IF (EXISTS (SELECT *
              FROM msdb.dbo.systargetservers))
  BEGIN
    UPDATE #filtered_jobs
    SET last_run_date = sjs.last_run_date,
        last_run_time = sjs.last_run_time,
        last_run_outcome = sjs.last_run_outcome
    FROM #filtered_jobs         fj,
         msdb.dbo.sysjobservers sjs
    WHERE (CONVERT(FLOAT, sjs.last_run_date) * 1000000) + sjs.last_run_time =
           (SELECT MAX((CONVERT(FLOAT, last_run_date) * 1000000) + last_run_time)
            FROM msdb.dbo.sysjobservers
            WHERE (job_id = sjs.job_id))
      AND (fj.job_id = sjs.job_id)
  END
  ELSE
  BEGIN
    UPDATE #filtered_jobs
    SET last_run_date = sjs.last_run_date,
        last_run_time = sjs.last_run_time,
        last_run_outcome = sjs.last_run_outcome
    FROM #filtered_jobs         fj,
         msdb.dbo.sysjobservers sjs
    WHERE (fj.job_id = sjs.job_id)
  END

  -- Step 3.4 : Set the type of the job to local (1) or multi-server (2)
  --            NOTE: If the job has no jobservers then it wil have a type of 0 meaning
  --                  unknown.  This is marginally inconsistent with the behaviour of
  --                  defaulting the category of a new job to [Uncategorized (Local)], but
  --                  prevents incompletely defined jobs from erroneously showing up as valid
  --                  local jobs.
  UPDATE #filtered_jobs
  SET type = 1 -- LOCAL
  FROM #filtered_jobs         fj,
       msdb.dbo.sysjobservers sjs
  WHERE (fj.job_id = sjs.job_id)
    AND (server_id = 0)
  UPDATE #filtered_jobs
  SET type = 2 -- MULTI-SERVER
  FROM #filtered_jobs         fj,
       msdb.dbo.sysjobservers sjs
  WHERE (fj.job_id = sjs.job_id)
    AND (server_id <> 0)

  -- Step 4: Filter on job_type
  IF (@job_type IS NOT NULL)
  BEGIN
    IF (UPPER(@job_type) = 'LOCAL')
      DELETE FROM #filtered_jobs
      WHERE (type <> 1) -- IE. Delete all the non-local jobs
    IF (UPPER(@job_type) = 'MULTI-SERVER')
      DELETE FROM #filtered_jobs
      WHERE (type <> 2) -- IE. Delete all the non-multi-server jobs
  END

  -- Step 5: Filter on dates
  IF (@date_comparator IS NOT NULL)
  BEGIN
    IF (@date_created IS NOT NULL)
    BEGIN
      IF (@date_comparator = '=')
        DELETE FROM #filtered_jobs WHERE (date_created <> @date_created)
      IF (@date_comparator = '>')
        DELETE FROM #filtered_jobs WHERE (date_created <= @date_created)
      IF (@date_comparator = '<')
        DELETE FROM #filtered_jobs WHERE (date_created >= @date_created)
    END
    IF (@date_last_modified IS NOT NULL)
    BEGIN
      IF (@date_comparator = '=')
        DELETE FROM #filtered_jobs WHERE (date_last_modified <> @date_last_modified)
      IF (@date_comparator = '>')
        DELETE FROM #filtered_jobs WHERE (date_last_modified <= @date_last_modified)
      IF (@date_comparator = '<')
        DELETE FROM #filtered_jobs WHERE (date_last_modified >= @date_last_modified)
    END
  END

  -- Return the result set (NOTE: No filtering occurs here)
  SELECT sjv.job_id,
         sjv.originating_server,
         sjv.name,
         sjv.enabled,
         sjv.description,
         sjv.start_step_id,
         category = ISNULL(sc.name, FORMATMESSAGE(14205)),
         owner = SUSER_SNAME(sjv.owner_sid),
         sjv.notify_level_eventlog,
         sjv.notify_level_email,
         sjv.notify_level_netsend,
         sjv.notify_level_page,
         notify_email_operator   = ISNULL(so1.name, FORMATMESSAGE(14205)),
         notify_netsend_operator = ISNULL(so2.name, FORMATMESSAGE(14205)),
         notify_page_operator    = ISNULL(so3.name, FORMATMESSAGE(14205)),
         sjv.delete_level,
         sjv.date_created,
         sjv.date_modified,
         sjv.version_number,
         fj.last_run_date,
         fj.last_run_time,
         fj.last_run_outcome,
         next_run_date = ISNULL(fj.next_run_date, 0),                                 -- This column will be NULL if the job is non-local
         next_run_time = ISNULL(fj.next_run_time, 0),                                 -- This column will be NULL if the job is non-local
         next_run_schedule_id = ISNULL(fj.next_run_schedule_id, 0),                   -- This column will be NULL if the job is non-local
         current_execution_status = ISNULL(fj.current_execution_status, 0),           -- This column will be NULL if the job is non-local
         current_execution_step = ISNULL(fj.current_execution_step, N'0 ' + FORMATMESSAGE(14205)), -- This column will be NULL if the job is non-local
         current_retry_attempt = ISNULL(fj.current_retry_attempt, 0),                 -- This column will be NULL if the job is non-local
         has_step = (SELECT COUNT(*)
                     FROM msdb.dbo.sysjobsteps sjst
                     WHERE (sjst.job_id = sjv.job_id)),
         has_schedule = (SELECT COUNT(*)
                         FROM msdb.dbo.sysjobschedules sjsch
                         WHERE (sjsch.job_id = sjv.job_id)),
         has_target = (SELECT COUNT(*)
                       FROM msdb.dbo.sysjobservers sjs
                       WHERE (sjs.job_id = sjv.job_id)),
         type = fj.type
  FROM #filtered_jobs                         fj
       LEFT OUTER JOIN msdb.dbo.sysjobs_view  sjv ON (fj.job_id = sjv.job_id)
       LEFT OUTER JOIN msdb.dbo.sysoperators  so1 ON (sjv.notify_email_operator_id = so1.id)
       LEFT OUTER JOIN msdb.dbo.sysoperators  so2 ON (sjv.notify_netsend_operator_id = so2.id)
       LEFT OUTER JOIN msdb.dbo.sysoperators  so3 ON (sjv.notify_page_operator_id = so3.id)
       LEFT OUTER JOIN msdb.dbo.syscategories sc  ON (sjv.category_id = sc.category_id)
  ORDER BY sjv.job_id

  -- Clean up
  DROP TABLE #job_execution_state
  DROP TABLE #filtered_jobs
  DROP TABLE #xp_results
END
go


use master
go

--------------------------------------------------------------------------------
-- SQLDMO stored procedures added after release into sqldmo.sql file

    -- sp_MSscriptdb_worker
    -- sp_MStablekeys
    -- sp_MSloginmappings
    -- sp_MSdbuseraccess
    -- sp_MSobjectprivs
    -- sp_MSforeachdb
    -- sp_MSforeachtable
--------------------------------------------------------------------------------

if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSscriptdb_worker')
    drop procedure sp_MSscriptdb_worker
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MStablekeys')
    drop procedure sp_MStablekeys
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSloginmappings')
    drop procedure sp_MSloginmappings
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSdbuseraccess')
    drop procedure sp_MSdbuseraccess
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSobjectprivs')
    drop procedure sp_MSobjectprivs
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSforeachdb')
    drop procedure sp_MSforeachdb
go
if exists (select * from master..sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSforeachtable')
    drop procedure sp_MSforeachtable
go


/*-----------------------------------------------------*/
/*-----------------------------------------------------*/
print N''
print N'Creating sp_MSforeachdb'
print N''
go

/*
 * The following table definition will be created by SQLDMO at start of each connection.
 * We don't create it here temporarily because we need it in Exec() or upgrade won't work.
 */

create proc sp_MSforeachdb
    @command1 nvarchar(2000), @replacechar nchar(1) = N'?', @command2 nvarchar(2000) = null, @command3 nvarchar(2000) = null,
    @precommand nvarchar(2000) = null, @postcommand nvarchar(2000) = null
as
    /* This proc returns one or more rows for each accessible db, with each db defaulting to its own result set */
    /* @precommand and @postcommand may be used to force a single result set via a temp table. */

    /* Preprocessor won't replace within quotes so have to use str(). */
    declare @inaccessible nvarchar(12), @invalidlogin nvarchar(12), @dbinaccessible nvarchar(12)
    select @inaccessible = ltrim(str(convert(int, 0x03e0), 11))
    select @invalidlogin = ltrim(str(convert(int, 0x40000000), 11))
    select @dbinaccessible = N'0x80000000'        /* SQLDMODbUserProf_InaccessibleDb; the negative number doesn't work in convert() */

    if (@precommand is not null)
        exec(@precommand)

    declare @origdb nvarchar(128)
    select @origdb = db_name()

    /* If it's a single user db and there's an entry for it in sysprocesses who isn't us, we can't use it. */
   /* Create the select */
    exec(N'declare hCForEach cursor global for select name from master..sysdatabases d ' +
            N' where (d.status & ' + @inaccessible + N' = 0)' +
            N' and ((DATABASEPROPERTY(d.name, ''issingleuser'') = 0 and (has_dbaccess(d.name) = 1)) or ' +
            N' ( DATABASEPROPERTY(d.name, ''issingleuser'') = 1 and not exists ' +
            N' (select * from master..sysprocesses p where dbid = d.dbid and p.spid <> @@spid)))' )

    declare @retval int
    select @retval = @@error
    if (@retval = 0)
        exec @retval = sp_MSforeach_worker @command1, @replacechar, @command2, @command3

    if (@retval = 0 and @postcommand is not null)
        exec(@postcommand)

   declare @tempdb nvarchar(258)
   SELECT @tempdb = REPLACE(@origdb, N']', N']]')
   exec (N'use ' + N'[' + @tempdb + N']')

    return @retval
go
/* End sp_MSforeachdb */

/*-----------------------------------------------------*/
/*-----------------------------------------------------*/
print N''
print N'Creating sp_MSforeachtable'
print N''
go

create proc sp_MSforeachtable
    @command1 nvarchar(2000), @replacechar nchar(1) = N'?', @command2 nvarchar(2000) = null,
   @command3 nvarchar(2000) = null, @whereand nvarchar(2000) = null,
    @precommand nvarchar(2000) = null, @postcommand nvarchar(2000) = null
as
    /* This proc returns one or more rows for each table (optionally, matching @where), with each table defaulting to its own result set */
    /* @precommand and @postcommand may be used to force a single result set via a temp table. */

    /* Preprocessor won't replace within quotes so have to use str(). */
    declare @mscat nvarchar(12)
    select @mscat = ltrim(str(convert(int, 0x0002)))

    if (@precommand is not null)
        exec(@precommand)

    /* Create the select */
   exec(N'declare hCForEach cursor global for select ''['' + REPLACE(user_name(uid), N'']'', N'']]'') + '']'' + ''.'' + ''['' + REPLACE(object_name(id), N'']'', N'']]'') + '']'' from sysobjects o '
         + N' where OBJECTPROPERTY(o.id, N''IsUserTable'') = 1 ' + N' and o.category & ' + @mscat + N' = 0 '
         + @whereand)
    declare @retval int
    select @retval = @@error
    if (@retval = 0)
        exec @retval = sp_MSforeach_worker @command1, @replacechar, @command2, @command3

    if (@retval = 0 and @postcommand is not null)
        exec(@postcommand)

    return @retval
go
/* End sp_MSforeachtable */



/*******************************************************************************/
print N''
print N'Creating sp_MSscriptdb_worker'
print N''
go
create procedure sp_MSscriptdb_worker
as
    set nocount on

   declare @PageSize int;
   select @PageSize = (low/1024) from master..spt_values where number = 1 and type = N'E'

    create table #tempFG
    (
     cDefault     int,                                  /* 1 for default FG, 0 for user defined */
     cDBFile      int,                                  /* 1 for DB file, 0 for Log file */
      cSize        int,                                  /* in 8K page */
      cMaxSize     int,
      cGrowth      int,
     cGrowthType  int,                                  /* 1 for GrowthInMB, 0 for GrowthInPercent */
     cFGName      nvarchar(132) NOT NULL,      /* FG name */
      cName        nchar(132) NOT NULL,         /* Logical */
      cFileName    nchar(264) NOT NULL,        /* Physical */
    )

   /* Default FileGroup first, which should cover all the log files */
   /* This one to pick up all the db files in Primary file group, while group id = 1 */
   insert #tempFG select 1, 1, (o.size * @PageSize)/1024, (case when (o.maxsize < 1) then o.maxsize else (o.maxsize * @PageSize)/1024 end), o.growth, (case when (o.status & 0x100000 = 0) then 1 else 0 end), g.groupname, o.name, o.filename from sysfiles o, sysfilegroups g where g.groupid = 1 and g.groupid = o.groupid and (o.status & 0x40) = 0
   /* This one to pick up all the log files in Primary file group, while group id = 0, note that group id 0 does not exist in sysfilegroups */
   insert #tempFG select 1, 0, (o.size * @PageSize)/1024, (case when (o.maxsize < 1) then o.maxsize else (o.maxsize * @PageSize)/1024 end), o.growth, (case when (o.status & 0x100000 = 0) then 1 else 0 end), N'PRIMARY', o.name, o.filename from sysfiles o where o.groupid = 0 and (o.status & 0x40) <> 0
   /* Other FileGroups, we should have DBFiles, no log files */

    create table #tempID
   (
        cGroupID int
    )
   insert #tempID select groupid from sysfilegroups where groupid <> 1

   declare @FGid int
    exec(N'declare hC cursor global for select cGroupID from #tempID')
    open hC
    fetch hC into @FGid
    while (@@fetch_status >= 0) begin
      insert #tempFG select 0, 1, (o.size * @PageSize)/1024, (case when (o.maxsize < 1) then o.maxsize else (o.maxsize * @PageSize)/1024 end), o.growth, (case when (o.status & 0x100000 = 0) then 1 else 0 end), g.groupname, o.name, o.filename from sysfiles o, sysfilegroups g where g.groupid = @FGid and g.groupid = o.groupid and (o.status & 0x40) = 0
      fetch hC into @FGid
   end
    deallocate hC

   select * from #tempFG
   DROP TABLE #tempFG

go
/* End sp_MSscriptdb_worker */


/*******************************************************************************/
print N''
print N'Creating sp_MStablekeys'
print N''
go

create procedure sp_MStablekeys
@tablename nvarchar(776) = null, @colname nvarchar(258) = null, @type int = null, @keyname nvarchar(517) = null, @flags int = null
as
    /* This proc returns the table's DRI keys.  @type is the type(s) of key(s) to return. */
    /* Make sure @type is only the key types (DRI_PRIMARYKEY, DRI_UNIQUE, DRI_REFERENCE). */
    if (@type is null)
        select @type = 0x000e
    else
        select @type = @type & 0x000e

    /* Flags usage:  For daVinci, to pass call thru to sp_MStablerefs. */
    if (@flags is null)
        select @flags = 0

    set nocount on
    create table #spkeys
    (
        cType                tinyint            NOT NULL,    /* key Type */
        cName                nvarchar(258)        NOT NULL,    /* key Name */
        cFlags                int                NULL,        /* e.g., 1 = clustered for PK/Unique */
        cColCount            int                NULL,        /* number of columns (or column pairs) in the key */
        cFillFactor            tinyint            NULL,        /* Fill factor of index creation */
        cRefTable            nvarchar(520)        NULL,        /* owner-qual Referenced table name for FKs */
        cRefKey                nvarchar(260)        NULL,        /* name of referenced key in referenced table */
            -- Note:  cConstID replaces the column list used in 6.0, for speed.
            -- The output set MUST replace this with either index_col(@tablename, cIndexID, 1-16) and NULL * 16
            -- (for PK/UQ) UNION col_name(r.fkeyid, r.fkey1-16) and col_name(r.rkeyid, r.rkey1-16), for SQLDMO,
            -- and these MUST BE nvarchar(132) for alignment in the SQLDMO cache structure!
        cConstID            int                NULL,        /* Reference constraint ID, if Foreign Key  */
        cIndexID            int                NULL,        /* ID of this key's index, if PK/UQ */
        cGroupName        sysname        NULL,        /* FileGroup name of this key, if PK/UQ */
        cDisabled        int                NULL,        /* 0 if enabled, 1 if disabled */
        cPrimaryFG        int                NULL,        /* 1 if primary FG, 0 otherwise */
    )

    declare @cType int, @cName nvarchar(258), @cFlags int, @cRefTable nvarchar(520), @fillfactor tinyint
    declare @objid int, @constid int, @indid int, @keycnt int, @q1 nvarchar(2000), @q2 nvarchar(2000), @objtype int, @groupname sysname
    declare @haskeytypes int, @wantkeytypes int
   declare @cDisabled int, @PrimaryFG int

    /* First see if @keyname was defined, and override @tablename and @type if so. */
    if (@keyname is not null)
    begin
         select @objid = id, @type = power(2, status & 0x0f) from sysconstraints where constid = object_id(@keyname)
         if (@objid is null)    begin
            RAISERROR (15001, -1, -1, @keyname)
            return 1
         end
         /* Now get the tablename for the index_col below */
         select @tablename = N'[' + REPLACE(user_name(uid), N']', N']]') + N']' + N'.' + N'[' + REPLACE(name, N']', N']]') + N']' from sysobjects where id = @objid
    end else begin
        /* Want all keys for this table (of @type type). */
        select @objid = id, @objtype = (case when OBJECTPROPERTY(id, N'IsTable') = 1 then 1 else 0 end), @haskeytypes = category & 0x0604
            from sysobjects where id = object_id(@tablename)
        if (@objid is null)    begin
            RAISERROR (15001, -1, -1, @tablename)
            return 1
        end
        if (@objtype <> 1)    begin
            RAISERROR (15218, -1, -1, @tablename)
            return 1
        end
        if @colname is not null and not exists (select * from syscolumns where id = @objid and name = @colname) begin
            RAISERROR (15253, -1, -1, @colname, @tablename)
            return 1
        end

        /* Skip cursor opening if we don't have any keys (of the type wanted); return a set anyway, for the cache. */
        if (@haskeytypes = 0)
            goto ReturnSet

        /* Map from the input bitmask to the category bitmask */
        select @wantkeytypes = 0
        if ((@type & power(2, 1)) <> 0)
            select @wantkeytypes = @wantkeytypes | 0x200
        if ((@type & power(2, 2)) <> 0)
            select @wantkeytypes = @wantkeytypes | 0x400
        if ((@type & power(2, 3)) <> 0)
            select @wantkeytypes = @wantkeytypes | 0x4
        if ((@haskeytypes & @wantkeytypes) = 0)
            goto ReturnSet
    end

    /* Preprocessor won't replace within quotes so have to use str(). */
    declare @sysgenname nvarchar(12), @pkstr nvarchar(12), @uqstr nvarchar(12), @fkstr nvarchar(12), @objtypebits nvarchar(12)
    select @sysgenname = ltrim(str(convert(int, 0x00020000)))
    select @pkstr = ltrim(str(convert(int, 1)))
    select @uqstr = ltrim(str(convert(int, 2)))
    select @fkstr = ltrim(str(convert(int, 3)))
    select @objtypebits = ltrim(str(convert(int, 0x0f)))

    /* Other ints we need strings for */
    declare @objidstr nvarchar(12), @typestr nvarchar(12)
    select @objidstr = ltrim(str(@objid))
    select @typestr = ltrim(str(@type))

    /* Qualifying key name. */
    declare @qualkeyname nvarchar(100)
    select @qualkeyname = null
    if (@keyname is not null) begin
      select @qualkeyname = N' and constid = object_id(''' + @keyname + N''')'
   end

    /*********************/
    /* Main cursor loop. */
    /*********************/
/*      exec(N'declare hC insensitive cursor for select constid, status & ' + @objtypebits + N', status & ' + @sysgenname +  */
    exec(N'declare hC cursor global for select constid, status & ' + @objtypebits + N', status & ' + @sysgenname +
            N' from sysconstraints where id = ' + @objidstr + N' and (' + @typestr + N' & power(2, status & 0x0f) != 0) ' + @qualkeyname)
    open hC
    fetch hC into @constid, @cType, @cFlags
    while (@@fetch_status >= 0) begin
        if (object_name(@constid) is null) begin
            raiserror 55555 N'Assert failed:  object_name(@constid) is null in sp_MStablekeys (pk/uq)'
            return 1
        end

        /* DRI_PRIMARYKEY, DRI_UNIQUE */
        if (@cType in (1, 2)) begin
            /* Get the index id enforcing this constraint. */
            select @indid = i.indid, @cName = o.name, @fillfactor = i.OrigFillFactor,
                    @cFlags = @cFlags | (case indid when 1 then 0x00000001 else 0 end),        /* test for clustered index */
               /* clustered index keys are part of non-clustered index key list, which cause incorrect sysindexes.keycnt */
                    @keycnt = case indid when 1 then keycnt else (select count(x.id) from sysindexkeys x where i.indid = x.indid and x.id = @objid) end,
               @groupname = f.groupname,
               @PrimaryFG = FILEGROUPPROPERTY( f.groupname, N'IsPrimaryFG' )
                from sysindexes i, sysobjects o, sysfilegroups f
            /* Use '=' instead of 'LIKE' in comparision, so we can handle wide card character correctly */
                where o.id = @constid and i.name = o.name and i.status & 0x1800 <> 0 and i.groupid = f.groupid
            if (@indid is null) begin
                raiserror 77777 N'Assert failed:  @indid is null in sp_MStablekeys (pk/uq)'
                return 1
            end

            /* Load our temp table. */
            insert #spkeys values (@cType, @cName, @cFlags, @keycnt, @fillfactor, null, null, null, @indid, @groupname, 0, @PrimaryFG)
        end

        /* DRI_REFERENCE */
        else if (@cType in (3)) begin
            /* Get the key column information from sysreferences. */
         select @keycnt = r.keycnt, @cName = object_name(r.constid), @cRefTable = N'[' + user_name(o.uid) + N']' + N'.' + N'[' + o.name + N']',
               @cDisabled = OBJECTPROPERTY( r.constid, N'CnstIsDisabled' )
            from sysreferences r, sysobjects o where r.constid = @constid and o.id = r.rkeyid

            /* Follow r.rkeyindid back to sysindexes to get the ref key name. */
            declare @cRefKey nvarchar(132)
            select @cRefKey = i.name, @cFlags = c.status from sysreferences r, sysindexes i, sysconstraints c
                where c.constid = r.constid and r.constid = @constid
                and i.id = r.rkeyid and i.indid = r.rkeyindid and i.status & 0x1800 <> 0

            /* Load our temp table. */
            insert #spkeys values (@cType, @cName, @cFlags, @keycnt, null, @cRefTable, @cRefKey, @constid, null, null, @cDisabled, 0)
        end        /* Key type */

        /* Get the next row. */
        fetch hC into @constid, @cType, @cFlags
    end            /* PRIMARY/UNIQUE */
    deallocate hC

    /* Now output the data */
ReturnSet:
    set nocount off
    select cType, cName, cFlags, cColCount, cFillFactor, cRefTable, cRefKey,
            cKeyCol1 = convert(nvarchar(132), index_col(@tablename, cIndexID, 1)),
            cKeyCol2 = convert(nvarchar(132), index_col(@tablename, cIndexID, 2)),
            cKeyCol3 = convert(nvarchar(132), index_col(@tablename, cIndexID, 3)),
            cKeyCol4 = convert(nvarchar(132), index_col(@tablename, cIndexID, 4)),
            cKeyCol5 = convert(nvarchar(132), index_col(@tablename, cIndexID, 5)),
            cKeyCol6 = convert(nvarchar(132), index_col(@tablename, cIndexID, 6)),
            cKeyCol7 = convert(nvarchar(132), index_col(@tablename, cIndexID, 7)),
            cKeyCol8 = convert(nvarchar(132), index_col(@tablename, cIndexID, 8)),
            cKeyCol9 = convert(nvarchar(132), index_col(@tablename, cIndexID, 9)),
            cKeyCol10 = convert(nvarchar(132), index_col(@tablename, cIndexID, 10)),
            cKeyCol11 = convert(nvarchar(132), index_col(@tablename, cIndexID, 11)),
            cKeyCol12 = convert(nvarchar(132), index_col(@tablename, cIndexID, 12)),
            cKeyCol13 = convert(nvarchar(132), index_col(@tablename, cIndexID, 13)),
            cKeyCol14 = convert(nvarchar(132), index_col(@tablename, cIndexID, 14)),
            cKeyCol15 = convert(nvarchar(132), index_col(@tablename, cIndexID, 15)),
            cKeyCol16 = convert(nvarchar(132), index_col(@tablename, cIndexID, 16)),
            cRefCol1 = convert(nvarchar(132), null),
            cRefCol2 = convert(nvarchar(132), null),
            cRefCol3 = convert(nvarchar(132), null),
            cRefCol4 = convert(nvarchar(132), null),
            cRefCol5 = convert(nvarchar(132), null),
            cRefCol6 = convert(nvarchar(132), null),
            cRefCol7 = convert(nvarchar(132), null),
            cRefCol8 = convert(nvarchar(132), null),
            cRefCol9 = convert(nvarchar(132), null),
            cRefCol10 = convert(nvarchar(132), null),
            cRefCol11 = convert(nvarchar(132), null),
            cRefCol12 = convert(nvarchar(132), null),
            cRefCol13 = convert(nvarchar(132), null),
            cRefCol14 = convert(nvarchar(132), null),
            cRefCol15 = convert(nvarchar(132), null),
            cRefCol16 = convert(nvarchar(132), null),
            cIndexID,
            cGroupName,
         cDisabled,
          cPrimaryFG
        from #spkeys where cType in (1, 2)
            and (@colname is null or
                index_col(@tablename, cIndexID, 1) = @colname or
                index_col(@tablename, cIndexID, 2) = @colname or
                index_col(@tablename, cIndexID, 3) = @colname or
                index_col(@tablename, cIndexID, 4) = @colname or
                index_col(@tablename, cIndexID, 5) = @colname or
                index_col(@tablename, cIndexID, 6) = @colname or
                index_col(@tablename, cIndexID, 7) = @colname or
                index_col(@tablename, cIndexID, 8) = @colname or
                index_col(@tablename, cIndexID, 9) = @colname or
                index_col(@tablename, cIndexID, 10) = @colname or
                index_col(@tablename, cIndexID, 11) = @colname or
                index_col(@tablename, cIndexID, 12) = @colname or
                index_col(@tablename, cIndexID, 13) = @colname or
                index_col(@tablename, cIndexID, 14) = @colname or
                index_col(@tablename, cIndexID, 15) = @colname or
                index_col(@tablename, cIndexID, 16) = @colname
            )
        UNION
        select c.cType, c.cName, c.cFlags, c.cColCount, c.cFillFactor, c.cRefTable, c.cRefKey,
            cKeyCol1 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey1)),
            cKeyCol2 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey2)),
            cKeyCol3 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey3)),
            cKeyCol4 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey4)),
            cKeyCol5 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey5)),
            cKeyCol6 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey6)),
            cKeyCol7 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey7)),
            cKeyCol8 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey8)),
            cKeyCol9 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey9)),
            cKeyCol10 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey10)),
            cKeyCol11 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey11)),
            cKeyCol12 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey12)),
            cKeyCol13 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey13)),
            cKeyCol14 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey14)),
            cKeyCol15 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey15)),
            cKeyCol16 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey16)),
            cRefCol1 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey1)),
            cRefCol2 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey2)),
            cRefCol3 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey3)),
            cRefCol4 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey4)),
            cRefCol5 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey5)),
            cRefCol6 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey6)),
            cRefCol7 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey7)),
            cRefCol8 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey8)),
            cRefCol9 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey9)),
            cRefCol10 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey10)),
            cRefCol11 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey11)),
            cRefCol12 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey12)),
            cRefCol13 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey13)),
            cRefCol14 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey14)),
            cRefCol15 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey15)),
            cRefCol16 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey16)),
            cIndexID,
            cGroupName,
         cDisabled,
          cPrimaryFG
        from #spkeys c, sysreferences r where c.cType = 3 and r.constid = c.cConstID
            and (@colname is null or
                col_name(r.fkeyid, r.fkey1) = @colname or
                col_name(r.fkeyid, r.fkey2) = @colname or
                col_name(r.fkeyid, r.fkey3) = @colname or
                col_name(r.fkeyid, r.fkey4) = @colname or
                col_name(r.fkeyid, r.fkey5) = @colname or
                col_name(r.fkeyid, r.fkey6) = @colname or
                col_name(r.fkeyid, r.fkey7) = @colname or
                col_name(r.fkeyid, r.fkey8) = @colname or
                col_name(r.fkeyid, r.fkey9) = @colname or
                col_name(r.fkeyid, r.fkey10) = @colname or
                col_name(r.fkeyid, r.fkey11) = @colname or
                col_name(r.fkeyid, r.fkey12) = @colname or
                col_name(r.fkeyid, r.fkey13) = @colname or
                col_name(r.fkeyid, r.fkey14) = @colname or
                col_name(r.fkeyid, r.fkey15) = @colname or
                col_name(r.fkeyid, r.fkey16) = @colname
            )
        order by cType, cName

    if (@flags & 1 <> 0)
        exec sp_MStablerefs @tablename, N'actualkeycols', N'foreign'

go
/* End sp_MStablekeys */


/*******************************************************************************/

print N''
print N'Creating sp_MSloginmappings'
print N''
go



create proc sp_MSloginmappings
    @loginname nvarchar(258) = null, @flags int = 0
as
    /*
     * @flags bits:
     *        0x01    - current db only
     */
    /*
     * Added @dbname so dbo can see everyone in current database.
     * Use hacky 4.21 syntax so it will run there, instead of a case..when.
     */
    declare @checkmultilogin int
    select @checkmultilogin = 1
    if ((@flags & 0x01 <> 0) and user_id() = 1)
        select @checkmultilogin = 0

    declare @logincount int
    select @logincount = 0
    if (@loginname is not null)
        select @logincount = count(*) from syslogins where loginname = @loginname

    /* Gotta be sa or dbo to see other than just current login. */
    declare @numlogins int, @whereloginname nvarchar(258), @name nvarchar(258), @retval int
    if (@loginname is null)
        select @numlogins = 2
    else
        select @numlogins = count(*) from syslogins where loginname = @loginname

    if (@numlogins = 0) begin
        RAISERROR (15007, -1, -1, @loginname)        /* Login not found */
        return 1
    end
    if (@checkmultilogin <> 0) begin
      /* We do not want to allow everybody to execute this SP */
        if (is_member(N'db_ddladmin') <> 1 and is_member(N'db_owner') <> 1 and is_member(N'db_accessadmin') <> 1 and is_member(N'db_securityadmin') <> 1 and (@numlogins > 1 or suser_sid() <> suser_sid(@loginname))) begin
            RAISERROR (14301, -1, -1, N'')                /* Only sa can see other than the current login */
            return 1
        end
    end
    if (@loginname is not null)
        select @whereloginname = N' and loginname = ''' + @loginname + N''''
   else
      select @whereloginname = N' '

    /*
     * This proc returns a result set with one or more rows for each database for which a login is a user or aliased to one.
     * If loginname is specified, the results are limited to that login.  First load a temp table with all logins that are
     * in a db, then add those which aren't mapped to any db.
     */
    create table #loginmappings(
        LoginName            nvarchar(128)        NULL,
        DBName                nvarchar(128)        NULL,
        UserName            nvarchar(128)        NULL,
        AliasName            nvarchar(128)        NULL
    )
    if (@flags & 0x01 <> 0) begin
        INSERT #loginmappings select l.loginname, db_name(), u.name, null from master..syslogins l, sysusers u where l.sid = u.sid and l.loginname is not NULL
        /*
         * We only allow multi-db on a 6.x server because dynamic exec() didn't exist before then,
         * hence there is no way to loop thru every database.  This is caught in SQLDMO so no
         * need for error message here; we'll just return no result sets.
         */
    end else begin
        exec @retval = sp_MSforeachdb
            N'use [?] INSERT #loginmappings select l.loginname, db_name(), u.name, null from master..syslogins l, sysusers u where l.sid = u.sid and l.loginname is not NULL'
        if (@retval <> 0)
            return 1
        insert #loginmappings select l.loginname, null, null, null from master..syslogins l where l.loginname not in (select LoginName from #loginmappings) and l.loginname is not NULL
    end

    /*
     * Now bring them out by loginname, each in its own result set.
     * If this is for all logins, we'll return all logins; if for curdb,
     * only those in #loginmappings (i.e. only those mapped in curdb).
     */
    exec(N'declare hCForEachLogin cursor global for select loginname from master..syslogins where loginname is not NULL ' + @whereloginname + N' order by loginname')
    if (@@error = 0)
        open hCForEachLogin
    if (@@error <> 0)
        return @@error
    fetch hCForEachLogin into @name
    while (@@fetch_status >= 0) begin
      /* Use '=' instead of 'LIKE' in comparision, so we can handle wide card character correctly */
        if ((@flags & 0x01 = 0) or exists (select * from #loginmappings where LoginName = @name))
            select * from #loginmappings where LoginName = @name
        fetch hCForEachLogin into @name
    end /* FETCH_SUCCESS */
    close hCForEachLogin
    deallocate hCForEachLogin
    return @@error
go
/* End sp_MSloginmappings */


/*******************************************************************************/
/*******************************************************************************/
/* exec sp_MSdbuseraccess 'perm', 'dbname' -- selecting priv bit from specified db                       */
/* exec sp_MSdbuseraccess 'db', 'dbname'   -- select databases, need to change db if dbname is specified */
/* exec sp_MSdbuseraccess 'init', 'dbname' -- noop                                                       */
/*******************************************************************************/
print N''
print N'Creating sp_MSdbuseraccess'
print N''
go

create proc sp_MSdbuseraccess
    @mode nvarchar(10) = N'perm', @qual nvarchar(128) = N'%'
as

   set nocount on

   declare @accessbit int
    if (lower(@mode) like N'perm%') begin
      /* verify */
      declare @id int, @stat int, @inval int
      select @id = dbid, @stat = status from master..sysdatabases where name = @qual
      if (@id is null) begin
         RAISERROR (15001, -1, -1, @qual)
         return 1
      end

      /* Can we access this db? */
      declare @single int
      select @single = DATABASEPROPERTY( @qual, N'issingleuser' )
/*      if ((@single <> 0) or ((@stat & SQLDMODBStat_Inaccessible) <> 0)) begin  */
      if ((@single <> 0) or
         (DATABASEPROPERTY(@qual, N'isdetached') <> 0) or
         (DATABASEPROPERTY(@qual, N'isshutdown') <> 0) or
         (DATABASEPROPERTY(@qual, N'issuspect') <> 0) or
         (DATABASEPROPERTY(@qual, N'isoffline') <> 0) or
         (DATABASEPROPERTY(@qual, N'isinload') <> 0) or
         (DATABASEPROPERTY(@qual, N'isinrecovery') <> 0) or
         (DATABASEPROPERTY(@qual, N'isnotrecovered') <> 0)) begin
         select @inval = 0x80000000
         select @inval
         return 0
      end
      select @accessbit = has_dbaccess(@qual)
      if ( @accessbit <> 1) begin
         select @inval = 0x40000000
         select @inval
         return 0
      end

      /** OK, we can access this db, need to go to the specified database to get priv bit **/
      declare @dbTempname nvarchar(258)
      declare @tempindex int
      SELECT @dbTempname = REPLACE(@qual, N']', N']]')
      exec (N'[' + @dbTempname + N']' + N'..sp_MSdbuserpriv ')
      return 0
   end

   /* If 'db', we want to know if what kind of access we have to the specified databases */
   /* If we are not in master, then we are selecting single database, we want to correct role bit to save round trip */
   if (lower(@mode) like N'db%') begin
      /* Make sure we're either in master or only doing it to current db. */
      declare @dbrole int
      select @dbrole = 0x0000

      if (db_id() <> 1)
         select @qual = db_name()

      /* If dbname contains ', double it for the cursor, since cursor statement is inside of '' */
      declare @qual2 nvarchar(128)
      SELECT @qual2 = REPLACE(@qual, N'''', N'''''')

      /* Preprocessor won't replace within quotes so have to use str(). */
      declare @invalidlogin nvarchar(12)
      select @invalidlogin = ltrim(str(convert(int, 0x40000000), 11))
      declare @inaccessible nvarchar(12)
      select @inaccessible = ltrim(str(convert(int, 0x80000000), 11))

      /* We can't 'use' a database with a version below the minimum. */
      /* SQL6.0 minimum is 406; SQL65 requires 408.  SQL70 database version is 408 now, it might change later */
      declare @mindbver smallint
      if (@@microsoftversion >= 0x07000000)
         select @mindbver = 408
      else
         select @mindbver = 406

      create table #TmpDbUserProfile (
         dbid        int NOT NULL PRIMARY KEY,
         accessperms int NOT NULL
         )

      /* Select all matching databases -- we want an entry even for inaccessible ones. */
      declare @dbid smallint, @dbidstr nvarchar(12), @dbstat int, @dbname nvarchar(258), @dbver smallint
      declare @dbbits int, @dbbitstr nvarchar(12)

      /* !!! work around here, if name contains '[', LIKE operator can't find it, since LIKE operator it treating '[' as a wild char */
      /* !!! but @qual2 might be '%', then = operator does not work */
      declare @temp int
      select @tempindex = charindex(N'[', @qual2)
      if (@tempindex <> 0)
         exec(N'declare hCdbs cursor global for select name, dbid, status, version from master..sysdatabases where name = N''' + @qual2 + N'''')
      else
         exec(N'declare hCdbs cursor global for select name, dbid, status, version from master..sysdatabases where name like N''' + @qual2 + N'''')

      open hCdbs

      /* Loop for each database, and if it's accessible, recursively call ourselves to add it. */
      fetch hCdbs into @dbname, @dbid, @dbstat, @dbver
      while (@@fetch_status >= 0) begin
         /* Preprocessor won't replace within quotes so have to use str(). */
         select @dbidstr = ltrim(str(convert(int, @dbid)))

         /* If it's a single user db and there's an entry for it in sysprocesses who isn't us, we can't use it. */
         declare @single_lockedout int
         select @single_lockedout = DATABASEPROPERTY( @dbname, N'issingleuser' )
         if (@single_lockedout <> 0)
            select @single_lockedout = 0 where not exists
               (select * from master..sysprocesses p where dbid = @dbid and p.spid <> @@spid)

         /* First see if the db is accessible (not in load, recovery, offline, single-use with another user besides us, etc.) */
/*         if ((@single_lockedout <> 0) or ((@dbstat & SQLDMODBStat_Inaccessible) <> 0) or (@dbver < @mindbver)) begin   */
         if ((@single_lockedout <> 0) or
            (@dbver < @mindbver) or
            (DATABASEPROPERTY(@dbname, N'isdetached') <> 0) or
            (DATABASEPROPERTY(@dbname, N'isshutdown') <> 0) or
            (DATABASEPROPERTY(@dbname, N'issuspect') <> 0) or
            (DATABASEPROPERTY(@dbname, N'isoffline') <> 0) or
            (DATABASEPROPERTY(@dbname, N'isinload') <> 0) or
            (DATABASEPROPERTY(@dbname, N'isinrecovery') <> 0) or
            (DATABASEPROPERTY(@dbname, N'isnotrecovered') <> 0) ) begin
            /* Inaccessible, but we can set dbo if we're sa or suser_id() is db owner sid. */
            exec (N'insert #TmpDbUserProfile values (' + @dbidstr + N', ' + @inaccessible + N')')
            end
         else begin
            /* Find out whether the current user has access to the database */
            select @accessbit = has_dbaccess(@dbname)
            if ( @accessbit <> 1) begin
               exec (N'insert #TmpDbUserProfile values (' + @dbidstr + N', ' + @invalidlogin + N')')
               end
            else begin
               /* Yes, current user does have access to this database, we are not trying to get priv at this point */
               select @dbbits = 0x01ff
               select @dbbitstr = ltrim(convert(nvarchar(12), @dbbits))
               exec (N'insert #TmpDbUserProfile values (' + @dbidstr + N', ' + @dbbitstr + N')')
               end
            end

         fetch hCdbs into @dbname, @dbid, @dbstat, @dbver
      end /* while FETCH_SUCCESS */
      close hCdbs
      deallocate hCdbs

      /* 1. If on all databases, then dbrole is dummy, need to get it later */
      /* 2. Do not double the ' character(s) in database name */
      /* 3. To speed up connection, accessperms column only indicate whether the login user can access the db, it does not contain */
      /*    permission info, we will retrieve the permission info through sp_MSdbuserpriv when necessary */
      /* !!! work around here, if name contains '[', LIKE operator can't find it, since LIKE operator it treating '[' as a wild char */
      /* !!! but @qual2 might be '%', then = operator does not work */
      if (@tempindex <> 0)
         select o.name, o.version, o.crdate, suser_sname(o.sid), o.dbid, lSize = 0, NonDbo = 0, Status = o.status, spaceavail = 0,
            LogOnSepDev = 1, o.category, t.accessperms, @dbrole, DatabaseProperty(o.name, 'isfulltextenabled'), o.status2 from master..sysdatabases o left outer join #TmpDbUserProfile t on t.dbid = o.dbid where o.name = @qual order by o.name
      else
         select o.name, o.version, o.crdate, suser_sname(o.sid), o.dbid, lSize = 0, NonDbo = 0, Status = o.status, spaceavail = 0,
            LogOnSepDev = 1, o.category, t.accessperms, @dbrole, DatabaseProperty(o.name, 'isfulltextenabled'), o.status2 from master..sysdatabases o left outer join #TmpDbUserProfile t on t.dbid = o.dbid where o.name like @qual order by o.name
      DROP TABLE #TmpDbUserProfile
      return 0
   end
go
/* End sp_MSdbuseraccess */

/*******************************************************************************/
print N''
print N'Creating sp_MSobjectprivs'
print N''
go

create proc sp_MSobjectprivs
    @objname nvarchar(776) = null,
    @mode nvarchar(10) = N'object',
    @objid int = null,
    @srvpriv int = null,
    @prottype int = null,
    @grantee nvarchar(258) = null,
   @flags int = 0,
   @rollup int = 0
as

    create table #objs(
        id  int NOT NULL
    )

    /* Temp table will hold output for final select */
    create table #output (
        action      int  NOT NULL,
        colid       int  NULL,
        uid         int  NOT NULL,
        protecttype int  NOT NULL,
        id          int  NOT NULL,
        grantor     int
    )

    create table #tmp(
        action   int   NOT NULL,
        uid      int   NOT NULL,
        protecttype int  NOT NULL,
    )

   /* mode    : 'object', 'user' or 'column'*/
   /*
    * Note:  This was expanded for 6.5 due to changes in sysprotects.columns usage, affecting
    * CPermission::ListPrivilegeColumns.  The following additional parameters are for this.
    */
   /* objid   : ID of the object we're querying */
   /* srvpriv : privilege that we're querying for (e.g. select) */
   /* prottype: Protect type, e.g. GRANT/REVOKE */
   /* grantee : Grantee name. */

   /*** @flags added for DaVinci uses.  If the bit isn't set, use 6.5 ***/
   /*** sp_MSobjectprivs '%s'                                         ***/

   /* 8.0: mode 'column', and grantee != null, we want user column level permissions for CTable/CView::ListUserColumnPermissions */
   /*      @rollup added to indicate special rollup result set for column level permission, set to 1 to roll up */

    /* @flags is for daVinci */
    if (@flags is null)
        select @flags = 0

    /* If @objid is not null, this is for the new query for perm cols. */
    if (@objid is not null) begin
        select u.name, o.name, a = col_name(p.id, a.number), a.low, a.high, a.number
            from master.dbo.spt_values a, dbo.sysprotects p, dbo.sysobjects o, dbo.sysusers u
            where p.id = @objid and p.action = @srvpriv and p.protecttype = @prottype
            and p.uid = user_id(@grantee)
            and p.columns != 0x01 and o.id = p.id and u.uid = o.uid
                and convert(tinyint, substring(isnull(p.columns, 0x01), a.low, 1)) &
                    -- 6.5 changed so that the bit 0 position is an "invert the bits" indicator:
                    --        when 0, behaviour is the same as in prior versions, and other bits
                    --            indicate columns with the specified privilege
                    --        when 1, the other bits are indicate columns lacking the specified privilege
                    a.high <> (case when (substring(isnull(p.columns, 0x00), 1, 1) & 1 = 0) then 0 else a.high end)
                    and col_name(p.id, a.number) is not null
                    and a.type = N'P' and a.number <= (select count(*) from dbo.syscolumns where id = @objid) order by a
        return 0
    end

    set nocount on

    /*
     * To get around a 4.21 subquery bug where returning count(*) of 0 (for proc cols)
     * causes the result set to return no rows, we need two passes; one to get the
     * objects, and another to explicitly use a value (@cols) instead of a subquery.
     */
    declare @id int, @uid int, @cols int
    select @id = null, @uid = null
    if (@mode like N'us%') begin
       select @uid = user_id(@objname)
   end else if (@mode like N'col%') and (@objname is null) and (@grantee is not null) begin
      /* 8.0, special path to get column level permissions from all objects on the specified user */
      select @uid = user_id(@grantee)
    end else begin
      select @id = object_id(@objname)
   end
    if (@id is null and @uid is null) begin
        RAISERROR (15001, -1, -1, @objname)
        return 1
    end

    /* Get a temp list of objects we're interested in.  Do not include repl_* users. */
   /* This is the original code */
   insert #objs select distinct p.id from dbo.sysprotects p
       where (@id is null or p.id = @id)
          and (@uid is null or p.uid = @uid)
       and p.action in (193, 195, 196, 197, 224, 26) and p.uid not in (16382, 16383)

    /* Use a "fake cursor" by deleting successive id's from #objs, as this must run on 4.21 */
    select @id = min(id) from #objs
    while (@id is not null) begin
        select @cols = count(*) from dbo.syscolumns c where c.id = @id
      /* sysprotects.columns is for SELECT and UPDATE, NULL if it is INSERT or DELETE, since INSERT and DELETE can not be applied to column level */
      insert #output select p.action, (case when p.columns is null then -1 else a.number end), p.uid, p.protecttype, p.id, p.grantor
         from master.dbo.spt_values a, dbo.sysprotects p
         where convert(tinyint, substring( isnull(p.columns, 0x01), a.low, 1)) & a.high !=0
         and (p.id = @id)
         and (@uid is null or p.uid = @uid)
         and a.number <= @cols
         and a.type = N'P'

      declare @count int, @whataction int, @whatid int, @dup int, @whatprot int

      /* First pass to correct duplicates */
      select @count = count(*) from #output where id = @id and colid in (0, -1) and protecttype in (205, 204)
      if ( @count > 0 ) begin
         /* We might have duplicate rows for permission on single coulmn(s) at this point */
         /* Use a fake cursor to remove the duplicates first. */
         insert #tmp select action, uid, protecttype from #output where id = @id and colid in (0, -1) and protecttype in (205, 204)
         select @whataction = min(action) from #tmp
         select @whatid = uid from #tmp where action = @whataction
         while (@whataction is not null) begin
            if (@mode like N'col%') and (@objname is null) and (@grantee is not null) begin
               /* Special case for column level permissions on ALL objects for the specified user, we don't want the row(s) on the entire table */
               /* and we don't want the possible duplicate rows in single column(s) */
               delete #output where (@whatid = uid) and (colid not in (0, -1)) and (protecttype in (205, 204)) and action = @whataction
                      and (exists (select * from #output where (@whatid = uid) and (colid in (0, -1)) and action = @whataction) and (id = @id))
               delete #output where (@whatid = uid) and (colid in (0, -1)) and (action = @whataction) and (id = @id)
            end else if (@mode like N'use%') and (@objname is not null) begin
               /* Special case for the user mode, we do want to keep the entire table permissions */
               delete #output where (@whatid = uid) and (colid not in (0, -1)) and (protecttype in (205, 204)) and action = @whataction and (id = @id)
            end else begin
               /* Other cases */
               delete #output where (@whatid = uid) and (colid not in (0, -1)) and (protecttype in (205, 204)) and action = @whataction
            end

            delete #tmp where @whatid = uid
            select @whataction = min(action) from #tmp
            select @whatid = uid from #tmp where action = @whataction
         end
         delete #tmp
      end

      /* Second pass to correct protect type */
      select @count = count(*) from #output where id = @id and colid in (0, -1)
      if ( @count > 0 ) begin
         /* use another fake cursor to correct the protecttype */
         /* if there are multiple rows in #output for the same id and action, and if colid = 0 exist */
         /* then other rows should have different protecttype from the one in colid = 0 row */
         insert #tmp select action, uid, protecttype from #output where id = @id and colid in (0, -1)
         select @whataction = min(action) from #tmp
         select @whatid = uid from #tmp where action = @whataction
         select @whatprot = protecttype from #tmp where uid = @whatid and action = @whataction
         while (@whataction is not null) begin
               delete #output where id = @id and colid not in (0, -1) and @whataction = action and @whatid = uid and @whatprot = protecttype
               delete #tmp where action = @whataction and @whatid = uid
               select @whataction = min(action) from #tmp
               select @whatid = uid from #tmp where action = @whataction
               select @whatprot = protecttype from #tmp where uid = @whatid and action = @whataction
         end
         delete #tmp
      end

        /* Increment our "fake cursor" column and get the next one. */
        delete #objs where id = @id
        select @id = min(id) from #objs
    end

    /*
     * Organize so that the non-collist privileges are returned first.. this allows
     * scripting to combine them.  sysprotects.action is tinyint, so the hibyte won't conflict.
     */

    update #output set action = action | 0x10000000 where colid <> 0

    /*
     *  BUG 58252
     *  Delete the columns that was droped
     */
    delete from #output where colid not in (0, -1) and col_name(id, colid) is null


    /*
     * Order output by uid so Public will script before other groups (we need to script privs for public before
     * other groups, before users; otherwise sysprotects doesn't hold onto things right).  Sub-order is by object id
     * so we know when we're done with one object and onto the next, then by protecttype to group all GRANTs and
     * REVOKEs together, and lastly by action (including ORDER_ACTION_BIT so scripting can be more efficient)
     * because we may have multiple rows for columns.
     */

    set nocount off
   if (@mode not like N'col%') begin
      /* Mode is not 'column', do the regular stuff */
       select p.action & ~convert(int, 0x10000000), N'column' = col_name(p.id, p.colid), p.uid, N'username' = user_name(p.uid),
               p.protecttype, o.name, N'owner' = user_name(o.uid), p.id, N'grantor' = user_name(p.grantor)
             from #output p, dbo.sysobjects o
             where o.id = p.id
             order by p.uid, p.id, p.protecttype, p.action
   end else
   /* Below are spcial cases for column level permissions */
   if (@objname is null) and (@grantee is not null) and (@rollup = 0) begin
      /* 8.0, special path to get column level permissions from all objects on the specified user */
      select N'ObjectName' = o.name, N'Owner' = user_name(o.uid), N'ColumnName' = col_name(p.id, p.colid), o.sysstat & 0x0f, p.id,
             p.action & ~convert(int, 0x10000000), p.protecttype
             from #output p, dbo.sysobjects o
             where p.id = o.id and p.uid = user_id(@grantee) and col_name(p.id, p.colid) is not null
             order by p.uid, p.id, p.protecttype, p.action
    end else if (@objname is not null) and (@grantee is not null) and (@rollup = 0) begin
      /* 8.0, mode 'column', and grantee != null, we want column level permissions on this object for this user */
      select N'column' = col_name(p.id, p.colid), N'owner' = user_name(o.uid), N'username' = user_name(p.uid), o.sysstat & 0x0f, p.id,
             p.action & ~convert(int, 0x10000000), p.protecttype
             from #output p, dbo.sysobjects o
             where o.id = p.id and p.uid = user_id(@grantee) and col_name(p.id, p.colid) is not null
             order by p.uid, p.id, p.protecttype, p.action
   end else if (@objname is not null) and (@grantee is null) and (@rollup = 0) begin
      /* 8.0, mode 'column', and grantee = null, we want column level permissions on this object for all users */
      select N'column' = col_name(p.id, p.colid), N'owner' = user_name(o.uid), N'username' = user_name(p.uid), o.sysstat & 0x0f, p.id,
             p.action & ~convert(int, 0x10000000), p.protecttype
             from #output p, dbo.sysobjects o
             where o.id = p.id and col_name(p.id, p.colid) is not null
             order by p.uid, p.id, p.protecttype, p.action
   end else if (@objname is null) and (@grantee is not null) and (@rollup <> 0) begin
      /* 8.0, roll up version of the special path to get column level permissions from all objects on the specified user */
      select distinct N'ObjectName' = o.name, N'owner' = user_name(o.uid),
             N'Select' = (case when ((p.action & ~convert(int, 0x10000000))=193) then 1 else 0 end),
             N'Update' = (case when ((p.action & ~convert(int, 0x10000000))=197) then 1 else 0 end),
             N'Type' = p.protecttype
             from #output p, dbo.sysobjects o
             where p.id = o.id and p.uid = user_id(@grantee) and col_name(p.id, p.colid) is not null
             order by o.name
   end else if (@objname is not null) and (@grantee is null) and (@rollup <> 0) begin
      /* 8.0, roll up version of the special path to return column level permissions on this object for all users */
      select distinct N'UserName' = user_name(p.uid),
             N'Select' = (case when ((p.action & ~convert(int, 0x10000000))=193) then 1 else 0 end),
             N'Update' = (case when ((p.action & ~convert(int, 0x10000000))=197) then 1 else 0 end),
             N'Type' = p.protecttype
             from #output p, dbo.sysobjects o
             where o.id = p.id and col_name(p.id, p.colid) is not null
             order by user_name(p.uid)
   end else begin
      raiserror 55555 N'Invalid parameter combinations.'
        return 1
   end
go
/* End sp_MSobjectprivs */


exec sp_MS_marksystemobject sp_MSscriptdb_worker
go
exec sp_MS_marksystemobject sp_MStablekeys
go
exec sp_MS_marksystemobject sp_MSloginmappings
go
exec sp_MS_marksystemobject sp_MSdbuseraccess
go
exec sp_MS_marksystemobject sp_MSobjectprivs
go
exec sp_MS_marksystemobject sp_MSforeachdb
go
exec sp_MS_marksystemobject sp_MSforeachtable
go


grant execute on sp_MSscriptdb_worker to public
grant execute on sp_MStablekeys to public
grant execute on sp_MSloginmappings to public
grant execute on sp_MSdbuseraccess to public
grant execute on sp_MSobjectprivs to public
grant execute on sp_MSforeachdb to public
grant execute on sp_MSforeachtable to public


--------------------------------------------------------------------------------
-- END SQLDMO SECTION

--------------------------------------------------------------------------------




--------------------------------------------------------------------------------
-- END OF FILE: Turn off marking of system objects.
--    DO NOT ADD ANYTHING AFTER THIS POINT
--------------------------------------------------------------------------------
exec sp_MS_upd_sysobj_category 2
go

exec sp_configure 'allow updates',0
go

reconfigure with override
go